3

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

7

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)
  ) ;
  • I ended up adding a restricted type column to the child table. – Andrew Hill Oct 18 '17 at 2:31
0

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'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.