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
