Foreign key
Overview
A foreign key is any column in a table which references the Primary key of another table.
Example
In NetForum, it is possibe to store multiple email addresses per customer. These email addresses are stored in co_email. Co_email, has a foreign key, eml_cst_key, which links the record back to the cst_key of the customer the email address is assigned to. Cst_key is the primary key of co_customer.
See Also
Data Model
Drop and Create Foreign Key Script
This script generates SQL to drop or create Foreign Keys for a specific table or all tables.
Parameters : @TableName specifies the table, if left empty generates for all tables. @CreateDropScript if the values is 0 , SQL to create Foreign Keys will be generated. If the value is 1, SQL to drop Foreign Keys will be generated.
Example:
exec SPGetFKs 'co_customer',0 -- this will generate the SQL to create all Foreign Keys that exist on co_customer or reference columns in co_customer.
exec SPGetFKs null,1 -- this will generate the SQL to drop all Foreign Keys for all tables.
CREATE PROCEDURE DBO.SPGetFKs
@TableName nvarchar(128) = NULL,
@CreateDropScript bit = 0
AS
SET NOCOUNT ON
DECLARE @FKName NVARCHAR(128)
DECLARE @FKColumnName NVARCHAR(128)
DECLARE @PKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE @fDeleteRule INT
DECLARE @FieldNames NVARCHAR(500)
CREATE TABLE #Temp(
PKTABLE_QUALIFIER NVARCHAR(128),
PKTABLE_OWNER NVARCHAR(128),
PKTABLE_NAME NVARCHAR(128),
PKCOLUMN_NAME NVARCHAR(128),
FKTABLE_QUALIFIER NVARCHAR(128),
FKTABLE_OWNER NVARCHAR(128),
FKTABLE_NAME NVARCHAR(128),
FKCOLUMN_NAME NVARCHAR(128),
KEY_SEQ INT,
UPDATE_RULE INT,
DELETE_RULE INT,
FK_NAME NVARCHAR(128),
PK_NAME NVARCHAR(128),
DEFERRABILITY INT)
DECLARE TTableNames CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype = 'U' AND (@TableName IS NULL OR name=@TableName)
OPEN TTableNames
FETCH NEXT
FROM TTableNames
INTO @fTableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #Temp
EXEC dbo.sp_fkeys @fTableName
FETCH NEXT
FROM TTableNames
INTO @fTableName
END
CLOSE TTableNames
DEALLOCATE TTableNames
SET @FieldNames = ''
SET @fTableName = ''
SELECT DISTINCT FK_NAME AS FKName,FKTABLE_NAME AS FTName,
@FieldNames AS FTFields,PKTABLE_NAME AS STName,
@FieldNames AS STFields,@FieldNames AS FKType
INTO #Temp1
FROM #Temp
ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME
DECLARE FK_CUSROR CURSOR FOR
SELECT FKName
FROM #Temp1
OPEN FK_CUSROR
FETCH
FROM FK_CUSROR INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE FK_FIELDS_CUSROR CURSOR FOR
SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE
FROM #TEMP
WHERE FK_NAME = @FKName
ORDER BY KEY_SEQ
OPEN FK_FIELDS_CUSROR
FETCH
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Temp1 SET FTFields = CASE WHEN LEN(FTFields)
= 0 THEN '['+@FKColumnName+']'
ELSE FTFields
+',['+@FKColumnName+']' END
WHERE FKName = @FKName
UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields)
= 0 THEN '['+@PKColumnName+']'
ELSE STFields
+',['+@PKColumnName+']' END
WHERE FKName = @FKName
FETCH NEXT
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
END
UPDATE #Temp1 SET FKType = CASE WHEN @fUpdateRule = 0
THEN FKType + ' ON UPDATE CASCADE'
ELSE FKType END
WHERE FKName = @FKName
UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0
THEN FKType + ' ON DELETE CASCADE'
ELSE FKType END
WHERE FKName = @FKName
CLOSE FK_FIELDS_CUSROR
DEALLOCATE FK_FIELDS_CUSROR
FETCH next
FROM FK_CUSROR INTO @FKName
END
CLOSE FK_CUSROR
DEALLOCATE FK_CUSROR
IF(@CreateDropScript=0)
BEGIN
SELECT 'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].['+FKName+']'') AND parent_object_id = OBJECT_ID(N''[dbo].['+FTName+']''))' +
' ALTER TABLE [dbo].['+FTName+'] ADD CONSTRAINT ['+FKName+'] FOREIGN KEY ('+FTFields+') REFERENCES ['+STName+'] ('+STFields+') '+ FKType
FROM #Temp1
END
else
BEGIN
SELECT 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].['+FKName+']'') AND parent_object_id = OBJECT_ID(N''[dbo].['+FTName+']''))' +
' ALTER TABLE [dbo].['+FTName+'] DROP CONSTRAINT ['+FKName+']'
FROM #Temp1
END
SET NOCOUNT OFF
RETURN
GO