Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
    
That was my initial inclination as well, and seeing that I work for a small company and rarely get outside opinions on menial things like this, this was good to know. Gracias. –  wootscootinboogie Jul 8 '13 at 14:27
1  
I typically define FKs inline as well, can you explain why those specifically are better to be created separately? –  Aaron Bertrand Jul 8 '13 at 16:11
    
@AaronBertrand: if you have a semi-automated process that creates all the tables from scripts (but in no particular order), then it's kinda hard to define the FK relationships until all the tables have been created. I know it's a semi-clever approach - but it's been working for a long time and so I leave it "as is" and just live with that one little drawback (having to create FK separately, after all tables have been created first) –  marc_s Jul 8 '13 at 17:13
    
Ah, that's true, I wasn't thinking of massive sets of CREATE TABLES, just individual ones. –  Aaron Bertrand Jul 8 '13 at 17:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.