Thursday, June 17, 2010

How to CREATE Foreign Key on existing table?

  • We can create a FOREIGN KEY constraint as part of the table definition when we create a table.
  • If a table already exists, we can add a FOREIGN KEY constraint, provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraints or UNIQUE constraint in any another or the same table.
Only 253 FOREIGN KEY constraint can be added to a table in SQL Server 2008. Click here to see the maximum sizes and numbers of various Database Objects defined in SQL Server 2008.

Creating FOREIGN Key on existing table
To explain this, I'll create a table TestFK without any Foreign Key. I will use Table TestPK to reference the Foreign Key. Below is T-SQL to create a TestFK table:

IF OBJECT_ID('TestFK','U') IS NOT NULL
DROP TABLE TestFK
GO
CREATE TABLE TestFK (
   [IDFK] [int] NULL,
   [Date] [datetime] DEFAULT (GETDATE())
)
GO
INSERT INTO TestFK (IDFK) VALUES
(1),(2),(1),(NULL),(2),(NULL),(3)
GO
SELECT [IDFK],[Date] FROM TestFK (NOLOCK)

Now ALTER the table to ADD Foreign Key
Execute below T-SQL code to add foreign key:

ALTER TABLE TestFK
ADD CONSTRAINT TestFK_IDFK FOREIGN KEY (IDFK)
REFERENCES TestPK (ID)

1 comment:

  1. This is a great post, but one thing I just learned about is secondary keys, which is covered well here too:

    http://www.programmerinterview.com/index.php/database-sql/secondary-key/

    ReplyDelete