Sql-server – When do you add your foreign keys

database-designprimary-keysql server

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?

Best Answer

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

Related Solutions

Sql-server – With MS SQL Server are the generated constraint names predictable

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar) 
  REFERENCES dbo.foo(bar));

Sql-server – Slow delete caused by many foreign keys

Make sure that there are indexes on the constrained columns, since the dbms will do a lookup using those columns in the referencing tables.

You can try using this script, it generates an index creation script for any non-indexed column being referenced by a cascading constraints.

SELECT 'CREATE NONCLUSTERED INDEX IX_'+OBJECT_NAME(fk.parent_object_id)+'_'+c.name+' ON '+OBJECT_NAME(fk.parent_object_id)+'('+c.name+') WITH (ONLINE=ON)' 
        --, OBJECT_NAME(fk.referenced_object_id) AS referenced_tale,  cc.name
FROM 
    sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.columns cc ON fkc.referenced_object_id = cc.object_id AND fkc.referenced_column_id = cc.column_id
WHERE delete_referential_action_desc IN ('CASCADE', 'SET_NULL')
    AND NOT EXISTS
        (
        SELECT  1
        FROM
            SYS.index_columns ic 
            INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i.index_id
        WHERE 
            1 = 1
            AND type_DESC IN ('CLUSTERED','NONCLUSTERED')
            AND ic.OBJECT_ID = c.object_id
            AND ic.column_id = c.column_id
            AND ic.is_included_column = 0
        )

Best Answer

Related Solutions

Sql-server – With MS SQL Server are the generated constraint names predictable

Sql-server – Slow delete caused by many foreign keys

Related Question