2 added 418 characters in body
source | link

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

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

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)
  ) ;
1
source | link

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