Using T-SQL to create tables programmatically
Part four of a four-part series of blogs

SQL doesn’t just select data; you can also use it to create tables and link them together with relationships. This blog shows you how!

  1. Creating Tables in SQL
  2. Creating Databases using SQL
  3. Creating a Table Programmatically in SQL
  4. Linking Tables (Foreign Keys and Relationships) (this blog)

This blog is part of a much longer tutorial on programming in SQL.  Alternatively, have a look at our classroom-based training courses in SQL.

Posted by Andy Brown on 05 October 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Linking Tables (Foreign Keys and Relationships)

This page of the blog shows you how to create links between two tables (or foreign key constraints, if yoiu want to be technical).

Our Example

Suppose we create two tables: one to hold authors, and one to hold books:

-- create table of authors

CREATE TABLE tblAuthor (

AuthorId int IDENTITY(1,1) PRIMARY KEY,

FirstName varchar(100),

LastName varchar(100)

)

 

-- add in a couple of authors

INSERT INTO tblAuthor(FirstName,LastName)

VALUES ('Stieg','Larsson')

INSERT INTO tblAuthor(FirstName,LastName)

VALUES ('John','Wyndham')

 

-- now create a table of books

CREATE TABLE tblBook(

BookId int IDENTITY(1,1) PRIMARY KEY,

BookName varchar(100) not null,

AuthorId int

)

-- add a few books

INSERT INTO tblBook(BookName,AuthorId)

VALUES ('The day of the Triffids',2)

INSERT INTO tblBook(BookName,AuthorId)

VALUES ('Girl with the dragon tattoo',1)

INSERT INTO tblBook(BookName,AuthorId)

VALUES ('The Chrysalids',2)

INSERT INTO tblBook(BookName,AuthorId)

VALUES ('The Kraken wakes',2)

INSERT INTO tblBook(BookName,AuthorId)

VALUES ('The girl who played with fire',1)

The SQL above would give us two tables, as yet unlinked:

Tables of authors and books

We need to link these tables by the AuthorId field.

 

We'll create SQL to join the two tables together:

Two tables joined together

The two tables should be linked by a relationship.

 

If you're not sure about the principles involved, have a look at my earlier blog on database design.

The SQL to Create Foreign Key Constraints

Here's how we could create the relationship above:

-- create relationship between the two tables, enforcing

-- referential integriy and cascade update/delete

ALTER TABLE tblBook

ADD CONSTRAINT fk_tblBook_tblAuthor

FOREIGN KEY (AuthorId)

REFERENCES tblAuthor(AuthorId)

 

-- optional extra settings to enforce cascade

-- update and delete

ON UPDATE CASCADE

ON DELETE CASCADE

This then means that you can't enter a book with an author id which doesn't exist in the table of authors (that is, you can't have orphan books).

  1. Creating Tables in SQL
  2. Creating Databases using SQL
  3. Creating a Table Programmatically in SQL
  4. Linking Tables (Foreign Keys and Relationships) (this blog)
This blog has 0 threads Add post