When do you add your foreign keys?

Question

I'm scripting out a fairly simple database so I can simply hand off a script to someone else for an application to use. I'm curious about where best practice/the community stand on this: When should you create your primary/foreign key constraints? Should you do it while you are creating the tables because of the continuity, or should you do it after the tables are created since it will be easier to script that way since you can create the tables in any order you wish? Are there any potential fatal flaws down the road with either pattern?

Answer 1

I typically create the primary key constraint directly with the table:

CREATE TABLE dbo.MyTable
(MyTableID INT IDENTITY(1,1) NOT NULL
     CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED,
 .......
)

I don't see any point in deferring that PK constraint to later (same also applies to DEFAULT and CHECK constraints which I typically also define "inline" with the table creation script).

However, the FK constraint, I typically define those as separate ALTER TABLE .. ADD CONSTRAINT ... statements, that can be run right after the table creation, or after all tables have been created - doesn't really matter:

ALTER TABLE dbo.MyTable WITH CHECK
ADD CONSTRAINT FK_MyTable_OtherTable
FOREIGN KEY (OtherTableID) REFERENCES dbo.OtherTable(OtherTableID)

ALTER TABLE dbo.MyTable CHECK CONSTRAINT FK_MyTable_OtherTable