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?
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
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