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

CREATETABLE tblAuthor (

AuthorId intIDENTITY(1,1) PRIMARYKEY,

FirstName varchar(100),

LastName varchar(100)

)

-- add in a couple of authors

INSERTINTO tblAuthor(FirstName,LastName)

VALUES ('Stieg','Larsson')

INSERTINTO tblAuthor(FirstName,LastName)

VALUES ('John','Wyndham')

-- now create a table of books

CREATETABLE tblBook(

BookId intIDENTITY(1,1) PRIMARYKEY,

BookName varchar(100) notnull,

AuthorId int

)

-- add a few books

INSERTINTO tblBook(BookName,AuthorId)

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

INSERTINTO tblBook(BookName,AuthorId)

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

INSERTINTO tblBook(BookName,AuthorId)

VALUES ('The Chrysalids',2)

INSERTINTO tblBook(BookName,AuthorId)

VALUES ('The Kraken wakes',2)

INSERTINTO tblBook(BookName,AuthorId)

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

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

We need to link these tables by the
AuthorId field.

We'll create SQL to join the two tables
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

ALTERTABLE tblBook

ADDCONSTRAINT fk_tblBook_tblAuthor

FOREIGNKEY (AuthorId)

REFERENCES tblAuthor(AuthorId)

-- optional extra settings to enforce cascade

-- update and delete

ONUPDATE CASCADE

ONDELETE 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).