Filtered foreign keys

I have two tables:

-
Contact (ID, Type);

-
Address (ID).

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)
  ) ;

and

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)
  ) ;

StackExchange Database Administrators Q#188379, answer score: 8