SQL Server Foreign Key to Multiple Tables

Foreign Key to multiple tables

You have a few options, all varying in "correctness" and ease of use. As always, the right design depends on your needs.

• You could simply create two columns in Ticket, OwnedByUserId and OwnedByGroupId, and have nullable Foreign Keys to each table.

• You could create M:M reference tables enabling both ticket:user and ticket:group relationships. Perhaps in future you will want to allow a single ticket to be owned by multiple users or groups? This design does not enforce that a ticket must be owned by a single entity only.

• You could create a default group for every user and have tickets simply owned by either a true Group or a User's default Group.

• Or (my choice) model an entity that acts as a base for both Users and Groups, and have tickets owned by that entity.

Heres a rough example using your posted schema:

create table dbo.PartyType
(   
    PartyTypeId tinyint primary key,
    PartyTypeName varchar(10)
)

insert into dbo.PartyType
    values(1, 'User'), (2, 'Group');

create table dbo.Party
(
    PartyId int identity(1,1) primary key,
    PartyTypeId tinyint references dbo.PartyType(PartyTypeId),
    unique (PartyId, PartyTypeId)
)

CREATE TABLE dbo.[Group]
(
    ID int primary key,
    Name varchar(50) NOT NULL,
    PartyTypeId as cast(2 as tinyint) persisted,
    foreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID)
)  

CREATE TABLE dbo.[User]
(
    ID int primary key,
    Name varchar(50) NOT NULL,
    PartyTypeId as cast(1 as tinyint) persisted,
    foreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID)
)

CREATE TABLE dbo.Ticket
(
    ID int primary key,
    [Owner] int NOT NULL references dbo.Party(PartyId),
    [Subject] varchar(50) NULL
)

Foreign Key to multiple tables at the same time

I highly recommend you use two columns, one per each foreign key. You probably want those columns to be nullable, so a row may "point" to one related table or the other (maybe even none, or both).

It's technically possible to use a single column that stores a foreign key to both related tables. However, you wouldn't be able to point to one or the other at will, since the foreign key constraint will enforce a non-null value to exist in both related tables always. You would need to remove the foreign key constraint, and that's a big no-no.

In short, this is how I see it:

clientPayment(
  rfcM varchar(12) references clientM, -- nullable by default
  rfcF varchar(13) references clientF, -- nullable by default
  constraint one_and_only_one_fk check (
    clientM is null and clientF is not null or
    clientM is not null and clientF is null
  ),
  some other data
)

The constraint prevents both fks to be null at the same time, or both to be not null at the same time.

How to add foreign keys in multiple table in ASP.NET Core MVC code-first approach?

This error is caused if you have data in your database conflicting your change. It is easily tested by creating a completely new migration and running it on a new empty database.

You most likely have old test data in your database which can't have a reference.

SQL Foreign key to two different tables

You can use a insert/update trigger on your Table_A.

Maybe something like this:

CREATE TRIGGER Table_a_trgr
    BEFORE INSERT OR UPDATE 
    on Table_a
    FOR EACH ROW
    DECLARE
      c_row NUMBER;
    BEGIN
      SELECT count(*) 
      INTO c_row
      FROM (
              SELECT ID FROM table_b WHERE id = :NEW.TableBCId
            UNION ALL
                SELECT ID FROM table_c WHERE İd = :NEW.TableBCId
          )
      ;
      IF c_row < 2 THEN
        raise_application_error(-20000, 'Error, Foreign Key');
      END IF;
    END;
    /

---

Related Topics