CODEWITHSUNDEEP
sqlsql-server
knowdotnet
knowdotnet

Reputation: 925

Value based Foreign Key in SQL Server

I have a 'MasterDetail' table containing columns Id, key, ItemName. I keep all enum values used in my application in this single table. e.g.

  1. Closed, StatusEnum
  2. In Progress, StatusEnum
  3. ScreenA, ScreenEnum
  4. Hold, StatusEnum

I have another table say 'Order' with one of the column as Status that reference this table for StatusEnum values. I want this table 'Order' to reference only values 1,2,4 from 'MasterDetail' table but not value 3 since that is ScreenEnum value.

I am using SQL server 2016. Any thoughts please.

Upvotes: 1

Views: 57

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

With this design, you will need the following to enforce referential integrity:

  • Add ItemName column to related table(s)
  • Add a composite unique constraint on MasterDetail Key and ItemName (or make that the primary key)
  • Add a composite foreign key to related table(s)

Alternatively, could limit allowable values with a column level check constraint that is a subset the MasterDetail values.

I generally recommend separate tables for each "enum" if you want to enforce referential integrity. Although that will require more tables, the design is a bit cleaner, IMHO.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

If I understand correctly, you can use a generated column to keep the item type information:

create table MasterDetail (
    Id int identity(1, 1) primary key,
    Key varchar(255) unique,
    ItemName varchar(255),
    constraint unq_masterdetail_key_ItemName unique (key, ItemName)
);

create table orders (
    OrderId int identity(1, 1) primary key,
    Key varchar(255),
    . . .
    StatusEnum as ('StatusEnum') persisted,
    constraint foreign key fk_orders_key_statusenum (key, statusenum) references masterdetail(key, itemname)
);

Note that the unique constraint in MasterDetail is redundant. But, it allows a foreign key relationship to refer to the key/itemname pair. That is what is needed to get types 1, 2, 4, but not 3.

Upvotes: 2

Related Questions