I have two tables:
Contact (ID, Type);
Is it possible to define a foreign key from Address (ID,'A') to Contact (ID,Type)?
Assuming that you want to implement a supertype/subtype relationship between the two tables (an
Address is a
Contact), you can, with a column having a fixed value of
'A' and use it for the foreign key:
CREATE TABLE Contact ( ID INT NOT NULL IDENTITY, Type VARCHAR(10) NOT NULL, PRIMARY KEY (ID, Type) ) ;
CREATE TABLE Address ( ID INT NOT NULL, Type VARCHAR(10) NOT NULL DEFAULT 'A', CHECK (Type = 'A'), PRIMARY KEY (ID, Type), FOREIGN KEY (ID, Type) REFERENCES Contact (ID, Type) ) ;
Alternatively we could declare
Type in Address as a persisted computed column (the check constraint would not be needed then):
Type AS CAST('A' AS varchar(10)) PERSISTED
CREATE TABLE Address ( ID INT NOT NULL, Type AS CAST('A' AS varchar(10)) PERSISTED NOT NULL, PRIMARY KEY (ID, Type), FOREIGN KEY (ID, Type) REFERENCES Contact (ID, Type) ) ;
A foreign key needs to reference a primary key or unique constraint.
Looking at your tables I assume you want Contacts to have an ID that exists in Address? If so, providing you have a
PRIMARY KEY or
UNIQUE constraint on the ID column in Address then...
ALTER TABLE Contacts WITH CHECK ADD CONSTRAINT [FK_Contacts_Address] FOREIGN KEY(ID) REFERENCES Address (ID)
If you are saying a Contact must have an address that has a type 'A' then you will need to use an
INSTEAD OF trigger or a stored procedure that runs on a schedule to list any Contacts that don't have Address type 'A'.