question

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!