enforce referential integrity with no primary key
Hi all i'm creating a little contacts database and i want to ensure referential integrity between the companyID columns in the two tables, however these are not the primary keys as the companyID is imported from an outside system. Please consider the following code :
CREATE TABLE #Company
(
ID INT PRIMARY KEY ,
CompanyID INT ,
Name NVARCHAR(50)
)
CREATE TABLE #Contacts
(
ContactID INT PRIMARY KEY ,
CompanyID INT ,
FirstName NVARCHAR(50) ,
LastName NVARCHAR(50)
)
INSERT INTO #Company
( ID, CompanyID, Name )
VALUES ( 1, -- ID - int
2, -- CompanyID - int
N'Bobs Plants' -- Name - nvarchar(50)
)
INSERT INTO #Company
( ID, CompanyID, Name )
VALUES ( 2, -- ID - int
5, -- CompanyID - int
N'Box company' -- Name - nvarchar(50)
)
INSERT INTO #Company
( ID, CompanyID, Name )
VALUES ( 3, -- ID - int
9, -- CompanyID - int
N'Brightwire' -- Name - nvarchar(50)
)
INSERT INTO #Company
( ID, CompanyID, Name )
VALUES ( 4, -- ID - int
10, -- CompanyID - int
N'Rebelstorage' -- Name - nvarchar(50)
)
INSERT INTO #Company
( ID, CompanyID, Name )
VALUES ( 5, -- ID - int
11, -- CompanyID - int
N'Luckysquirrel' -- Name - nvarchar(50)
)
INSERT INTO #Contacts
( ContactID ,
CompanyID ,
FirstName ,
LastName
)
VALUES ( 1 , -- ContactID - int
11 , -- CompanyID - int
N'Bob' , -- FirstName - nvarchar(50)
N'' -- LastName - nvarchar(50)
)
INSERT INTO #Contacts
( ContactID ,
CompanyID ,
FirstName ,
LastName
)
VALUES ( 2 , -- ContactID - int
9 , -- CompanyID - int
N'Joe' , -- FirstName - nvarchar(50)
N'' -- LastName - nvarchar(50)
)
INSERT INTO #Contacts
( ContactID ,
CompanyID ,
FirstName ,
LastName
)
VALUES ( 3 , -- ContactID - int
5 , -- CompanyID - int
N'Fred' , -- FirstName - nvarchar(50)
N'' -- LastName - nvarchar(50)
)
INSERT INTO #Contacts
( ContactID ,
CompanyID ,
FirstName ,
LastName
)
VALUES ( 4 , -- ContactID - int
3 , -- CompanyID - int
N'Marissa' , -- FirstName - nvarchar(50)
N'' -- LastName - nvarchar(50)
)
INSERT INTO #Contacts
( ContactID ,
CompanyID ,
FirstName ,
LastName
)
VALUES ( 5 , -- ContactID - int
2 , -- CompanyID - int
N'Claire' , -- FirstName - nvarchar(50)
N'' -- LastName - nvarchar(50)
)
How would i do this? Obviously i want to ensure that a contact is not entered against a company that doesn't exist! Thanks for your help!
Best Answer
Add a unique constraint to the Company table, such as
alter table dbo.Company add constraint companyid_Unique unique (CompanyID)
then you can define a foreign key from the Contacts table
alter table contacts
add constraint contacts_fk foreign key (companyid)
references company (CompanyID)
You don't need to reference a primary key in a FK relationship, just a UNIQUE constraint or index
i didn't know you could reference a unique constraint in a FK relationship! Thanks Kev that should do the trick!
