User Tools

Site Tools


notes:sql:foreignkey

FOREIGN KEY

Create a table with a mandatory (NOT NULL) foreign key:

-- Create a parent table.
CREATE TABLE Customer
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerName NVARCHAR(100)
)
 
-- Create a child table.
CREATE TABLE [ORDER]
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerId INT NOT NULL CONSTRAINT FK_Order_Customer FOREIGN KEY REFERENCES Customer(Id),
    Amount MONEY DEFAULT 0
)
 
-- Insert some data to the Order table.
-- Note that each Order must have a Customer assigned.
DECLARE @CustomerId INT
INSERT INTO Customer VALUES ('Customer #1')
SET @CustomerId=@@IDENTITY
INSERT INTO [ORDER] VALUES (@CustomerId, 24.00)
INSERT INTO [ORDER] VALUES (@CustomerId, 199.00)
INSERT INTO [ORDER] VALUES (@CustomerId, 256.00)
INSERT INTO Customer VALUES ('Customer #2')
SET @CustomerId=@@IDENTITY
INSERT INTO [ORDER] VALUES (@CustomerId, 120.00)
INSERT INTO Customer VALUES ('Customer #3')
SET @CustomerId=@@IDENTITY
INSERT INTO [ORDER] VALUES (@CustomerId, 88.00)
INSERT INTO [ORDER] VALUES (@CustomerId, 99.00)
 
-- This line fails with an error message:
-- Cannot insert the value NULL into column 'CustomerId', table 'Order'; 
-- column does not allow nulls. INSERT fails.
INSERT INTO [ORDER] VALUES (NULL, 13.00)
 
-- Show results
SELECT CustomerName, Amount
FROM Customer JOIN [ORDER] ON Customer.Id=[ORDER].CustomerId
 
CustomerName  Amount
------------  ------
Customer #1   24.00
Customer #1   199.00
Customer #1   256.00
Customer #2   120.00
Customer #3   88.00
Customer #3   99.00

Create a table with an optional  foreign key (allows NULL):

-- Method#1
CREATE TABLE [ORDER]
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerId INT NULL CONSTRAINT FK_Order_Customer FOREIGN KEY REFERENCES Customer(ID),
    Amount MONEY DEFAULT 0
)
GO
 
-- Method#2
CREATE TABLE [ORDER]
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerId INT NULL,
 
    -- Column-level foreign key
    CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerId) REFERENCES Customer(ID)
)
GO
 
-- It is possible to insert an Order without specifying a Customer
INSERT INTO [ORDER] VALUES (NULL, 13.00)
 
-- Show results (we need to use RIGHT JOIN to show the "orphan" record )
SELECT CustomerName, Amount
FROM Customer RIGHT JOIN [ORDER] ON Customer.Id=[ORDER].CustomerId
 
CustomerName  Amount
------------  ------
Customer #1   24.00
Customer #1   199.00
Customer #1   256.00
Customer #2   120.00
Customer #3   88.00
Customer #3   99.00
NULL          13.00   <-- the "orphan" record (an Order with no Customer)

Define a foreign-key on multiple columns:

-- Create a parent table
CREATE TABLE Region
(
    LanguageCode CHAR(2) NOT NULL,
    RegionCode CHAR(2) NOT NULL DEFAULT '',
    RegionDesc VARCHAR(50) NOT NULL DEFAULT '',
 
    -- Composite primary key    
    CONSTRAINT PK_Region PRIMARY KEY (LanguageCode, RegionCode)
)
GO
-- Create a child table with a foreign-key on multiple columns:
CREATE TABLE Customer
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerName NVARCHAR(12),
 
    LanguageCode CHAR(2) NOT NULL,
    RegionCode CHAR(2) NOT NULL DEFAULT '',
 
    -- Table-level foreign key
    CONSTRAINT FK_Customer_Region FOREIGN KEY (LanguageCode, RegionCode) 
        REFERENCES Region(LanguageCode, RegionCode)
)
GO
 
-- Insert test data
INSERT INTO Region VALUES ('en', '', 'English')
INSERT INTO Region VALUES ('en', 'AU', 'English - Australia')
INSERT INTO Region VALUES ('en', 'BZ', 'English - Belize')
INSERT INTO Region VALUES ('es', '', 'Spanish')
INSERT INTO Region VALUES ('es', 'AR', 'Spanish - Argentina')
INSERT INTO Region VALUES ('es', 'BO', 'Spanish - Bolivia')
 
INSERT INTO Customer VALUES ('Customer #1', 'en','BZ')
INSERT INTO Customer VALUES ('Customer #2', 'es','')
INSERT INTO Customer VALUES ('Customer #3', 'es','BO')
 
-- Show results:
SELECT CustomerName, RegionDesc
FROM Region JOIN Customer ON Region.LanguageCode=Customer.LanguageCode AND 
    Region.RegionCode=Customer.RegionCode
 
CustomerName RegionDesc
------------ -----------------
Customer #1  English - Belize
Customer #2  Spanish
Customer #3  Spanish - Bolivia

Use ALTER TABLE to create and manipulate foreign keys:

-- Add a foreign key on an existing table
ALTER TABLE [ORDER] WITH NOCHECK 
    ADD CONSTRAINT FK_Order_Customer FOREIGN KEY(CustomerId) REFERENCES Customer(Id)
 
-- Add a table-level foreign key on an existing table
ALTER TABLE Customer WITH CHECK 
    ADD CONSTRAINT FK_Customer_Region FOREIGN KEY(LanguageCode, RegionCode) 
    REFERENCES Region(LanguageCode, RegionCode)
 
-- Add a self-reference foreign key
ALTER TABLE Customer WITH NOCHECK 
    ADD CONSTRAINT FK_Customer_Customer FOREIGN KEY (Id) REFERENCES Customer(Id)
 
-- Disable a FOREIGN KEY constraint
ALTER TABLE [ORDER] NOCHECK CONSTRAINT FK_Order_Customer
 
-- Disable all CHECK and FOREIGN KEY constraints
ALTER TABLE [ORDER] NOCHECK CONSTRAINT ALL

A sproc to disable all foreign keys in the current database:

IF OBJECT_ID('wbs_DisableForeignKeys') IS NOT NULL DROP PROC wbs_DisableForeignKeys
GO
CREATE PROCEDURE wbs_DisableForeignKeys
AS
BEGIN
    DECLARE @cmd VARCHAR(1000)
    DECLARE @TableName SYSNAME, @ConstraintName SYSNAME
    DECLARE curKeys CURSOR FOR SELECT TABLE_NAME, CONSTRAINT_NAME 
                               FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                               WHERE CONSTRAINT_TYPE='FOREIGN KEY'
    OPEN curKeys
    FETCH NEXT FROM curKeys INTO @TableName, @ConstraintName
    WHILE @@FETCH_STATUS=0
    BEGIN
        -- Change NOCHECK to CHECK in order to enable the foreign keys.
        SET @cmd = 'ALTER TABLE [' + @TableName + '] NOCHECK CONSTRAINT [' + @ConstraintName + ']'
        PRINT @cmd
        EXEC(@cmd)
        FETCH NEXT FROM curKeys INTO @TableName, @ConstraintName
    END
    CLOSE curKeys
    DEALLOCATE curKeys
END
GO

Enable/disable foreign keys:

-- Disable all constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
 
-- Enable all constraints
EXEC sp_msforeachtable @command1='PRINT ''?''', @command2='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Identify a table owning a foreign key FK_NodeId:

SELECT OBJECT_NAME(parent_obj) 
FROM sysobjects 
WHERE [name] = 'FK_NodeId'
notes/sql/foreignkey.txt · Last modified: 2015/06/24 by admin