Foreign Key Referring to Primary Keys Across Multiple Tables

One foreign key referring to multiple table's primary keys

It is unusual in my experience but it potentially makes sense as a way to implement certain business rules with a meaning similar to "A must be both B and C".

One possible example: a StaffDiscount table identifying customers who are also employees and are eligible for a discount. Such a table might have a single foreign key that references both the Customer table and the Employee table. This does assume that a common key is used to identify both customers and employees.

CREATE TABLE StaffDiscount
 (CustomerEmployee INT NOT NULL PRIMARY KEY /* Same key identifies customer and employee */,
  FOREIGN KEY (CustomerEmployee) REFERENCES Customer (CustomerNum),
  FOREIGN KEY (CustomerEmployee) REFERENCES Employee (EmployeeNum));

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.

Add multiple primary keys to a table

I find one solution.

select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='avgEnt'
and type_desc LIKE '%CONSTRAINT'

Find constraint of table PK_avgent

After drop primary on table

-- drop current primary key constraint
ALTER TABLE dbo.avgEnt
DROP CONSTRAINT PK_avgent;
GO

After add two column

-- create new primary key constraint
ALTER TABLE dbo.avgEnt
ADD CONSTRAINT PK_avgent PRIMARY KEY NONCLUSTERED (SrNo, TrnDate);
GO

it is work form me.

---

Related Topics