SQL Server – Create two tables with primary and foreign key with SQL code

There is a simple way in VBA and in Python to create tables in the database you need, giving the tables a name of a variable and the type of a variable. In SQL Server, we also have a way to create tables from plain SQL text.

Let’s imagine that you have a task to create two tables with a one to many connection like this:

Thus, what do we have above? Two tables with a primary keys each and a connection between them. Nothing special, but the connection is really very basic, so I have decided that it worths blogging about it. To make the task one idea more difficult, after building the columns, I add additional column in table FootballTournaments with the command ALTER.

The SQL is really selbstverständlich and that is one of the SQL beauties. Here comes the code:

CREATE TABLE FootballTeams (
	FootballTeamID INT IDENTITY,
	Name nvarchar(50) NOT NULL,
	PlayersCount INT NOT NULL,
	AdministratorsCount INT NOT NULL,
	FoundedIn DATETIME NOT NULL
	CONSTRAINT PK_FootballTeams PRIMARY KEY(FootballTeamID))
GO

CREATE TABLE FootballTournaments (
	FootballTournamentID INT IDENTITY,
	Name nvarchar(50) NOT NULL,
	Budget INT,
	Country nvarchar(50) NOT NULL,
	TeamID INT NOT NULL,
	CONSTRAINT PK_FootballTournaments PRIMARY KEY(FootballTournamentID),
	CONSTRAINT FK_FootballTournaments_FootballTeams (TeamID) REFERENCES FootballTeams(FootballTeamID))
GO

ALTER TABLE FootballTournaments ADD President nvarchar(50) NOT NULL
GO

Enjoy it!