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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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!