Data Modeling: What is a good relational design when a table has several foreign key constrainst to a single table?

Question

Hi there!

I have 2 tables: 1. Employees 2. Vouchers

Employees table has a single primary key. Vouchers table has 3 foreign key constraints referencing the Employees table.

The following is a sample T-SQL script (not the actual table script) to create both tables and their relationship in SQL Server:

IF OBJECT_ID('dbo.Vouchers') IS NOT NULL
    DROP TABLE dbo.Vouchers
IF OBJECT_ID('dbo.Employees') IS NOT NULL
    DROP TABLE dbo.Employees
GO

CREATE TABLE Employees
(
  ObjectID     INT    NOT NULL   PRIMARY KEY    IDENTITY
)

CREATE TABLE Vouchers
(
  ObjectID     INT    NOT NULL   PRIMARY KEY    IDENTITY,
  IssuedBy     INT,
  ReceivedBy   INT,
  ApprovedBy   INT,

  CONSTRAINT fk_Vouchers_Employees_IssuedBy FOREIGN KEY (IssuedBy)
                                    REFERENCES Employees (ObjectID)
                                    ON UPDATE CASCADE
                                    ON DELETE NO ACTION,
 CONSTRAINT fk_Vouchers_Employees_ReceivedBy FOREIGN KEY (ReceivedBy)
                                    REFERENCES Employees (ObjectID)
                                    ON UPDATE CASCADE
                                    ON DELETE NO ACTION,
 CONSTRAINT fk_Vouchers_Employees_ApprovedBy FOREIGN KEY (ApprovedBy)
                                    REFERENCES Employees (ObjectID)
                                    ON UPDATE CASCADE
                                    ON DELETE NO ACTION 
)

But an error is thrown:

Msg 1785, Level 16, State 0, Line 7
Introducing FOREIGN KEY constraint 'fk_Vouchers_Employees_ReceivedBy' on table 'Vouchers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I don't have an idea of what efficient solution is available here. The requirements on the relationship is that: whenever an Employee is deleted, the Voucher that references some of its columns to the Employee does not get deleted (ON DELETE CASCADE is not an option). Instead, the values of the columns (IssuedBy, ReceivedBy and/or ApprovedBy) that are referenced to the deleted Employee should be set to NULL (since the columns are NULLABLE).

Many thanks!

Answer 1

Strictly from a relational design point of view, the Vouchers table as three Foreign Keys. Whether you choose to enforce them, through CASCADE assertions or otherwise, is an implementation issue, but the relational design still exists. Presumably you want to enforce that, if one of the three fields is not NULL, then a matching record needs to exist. Or not. It's an implementation issue as to whether or not you care to enforce the design.

However, the violations you describe are assumed at your peril. The fact that you're asking this question suggests you may not fully appreciate all the ways these choices can lead into the swamp.

I think the error may be a consequence of the fact that more than one of the three might refer to the same employee.

BTW, I've in very few cases ever found it necessary to delete records in such a fashion that CASCADES are useful. Usually that would be used to prevent the database from being too big; and database capacity is less and less an issue over time.

Answer 2

From a design standpoint it seems good to have the 3 foreign keys you listed. It looks like the error message you are getting relates to the ON UPDATE CASCADE options on your foreign keys (although I was able to create the table as specified). Regardless, to get the behavior you mention wanting, I would recommend a trigger on the Employees table, that fires before you delete the record. This trigger would find instances of the Employees.OjbectID in the Vouchers table and set them to NULL.

Answer 3

I would not actually delete Employees, but instead use a trigger to set a flag to mark them as deleted.

I generally don't turn on cascade of Updates or Deletes, but instead require an application to explicitly perform these actions.