Defining Foreign Keys at Table Create Time

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

How many of you can define a foreign key when you create the table? Probably a few of you, but I bet most of you are like me and don’t necessarily know the syntax. I have often defined these later, which is fine. As long as they get defined.

However I knew I needed a specific key when I was creating a table and couldn’t remember the syntax, so I had to search and learn how. I used Google and saw a few links from MSDN, but those tend to be overly documentation heavy. One of the links was to SQL Authority, run by Pinal Dave. He does a great job of simplifying things (and he’s a friend), so I followed that link. I could see the syntax and tested it in minutes.

It’s easy to create a Primary Key in CREATE TABLE, and I wrote about that for one of my first SQLNewBlogger posts. The Foreign Key is similar, but not quite as simple.

Imagine that I have a parent table:

CREATE TABLE orders ( orderid INT IDENTITY(1, 1) CONSTRAINT Orders_PK PRIMARY KEY ( orderid ) , orderdate DATETIME , complete BIT ); GO

I now want to create a child table and link the orderid in the child to the parent. I can do it like this:

CREATE TABLE OrderLines ( orderlineid INT IDENTITY(1, 1) CONSTRAINT OrderLines_PK PRIMARY KEY ( orderlineid ) , orderid INT CONSTRAINT orderlines_order_fk FOREIGN KEY REFERENCES orders ( orderid ) , qty INT ); GO

Note that I define a constraint inline, just as I did for the parent. However I note this one is an FK and it "references" another table. In this case, I list the Orders table and put the columns in parenthesis.

Quick, easy, build your FKs inline when you know about them in advance.

SQLNewBlogger

While trying to remember how to create an FK, I ran a search and chose the reference below to start. A matter of seconds had me seeing the syntax and writing the code.

Putting this together was less than ten minutes.

References

Creating Primary Key and Foreign Key Constraints – http://blog.sqlauthority.com/2008/09/08/sql-server-%E2%80%93-2008-creating-primary-key-foreign-key-and-default-constraint/

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

2 Responses to Defining Foreign Keys at Table Create Time

  1. Ray Herring says:

    I like to include all table constraints inline the the create table script. I usually include specific column constraints with the actual column definition (no comma in this case) and always include an explicit name for the constraint.
    Foreign Keys are a special case since they generally reference another table in the database. When I worked on a vendor’s team we scripted the foreign keys in separate scripts to run after all of the create table scripts ran. We found that to be the only way to manage the issue.
    Now, I work on the production side and prefer to include the FK definition in the Create script.

    I always have to look up the check – with check syntax (for both FK and CK). I find it very confusing;p But you do want to ensure your FK is “Trusted” otherwise it is just an adornment.

  2. way0utwest says:

    I think that’s great if you can do it, but what if you add a new object later and need a FK? Do you modify the child’s original code? Keep FKs separate?

    I think you can handle this multiple ways, but philosophically you have to choose a way to do this.

Comments are closed.