Enforce constraints between tables

Question

How do you establish a constraint where, one column (not the primary key) has to have the same value as another table's column. I'm not quite sure how to phrase it so here's an example:

Ex: I have three tables, Employee, Director, Division and Department

The structure for the tables are as follows:

Employee

• Id
• Name
• DirectorId (FK)
• DepartmentID (FK)

Director

• Id
• Name
• DepartmentID (FK)

Department

• Id
• Name
• DivisionId (FK)

Division

• Id
• Name

Employees and directors both have departments, each department has a division but their divisions have to be the same. Is there a way to enforce this? (Hopefully without having to resort to triggers)

Answer 1

Create stored procedures that will have proper GRANTS and don't allow user to INSERT into table directly. Use stored procedures as interface to the database, and check required conditions in them before insertion.

Answer 2

There is no limitation on creating foreign keys--there's nothing to stop you from defining a foreign key constraint on these tables:

• EMPLOYEE
• DIRECTOR

...associating them to the DEPARTMENT table. Though frankly, I don't see the need for a DIRECTOR table--that should be either a boolean indicator in the EMPLOYEE table, or possibly an EMPLOYEE_TYPE_CODE with it's own foreign key constraint to distinguish between employees and directors.

Multiple Foreign Keys

The presence of a foreign key also doesn't stop you from putting a second (or third, etc) constraint on the same column. Consider the scenario of TABLE_C.column having foreign key constraints to both TABLE_A.col and TABLE_B.col -- this is perfectly acceptable in the database, but it means that only values that exist in both TABLE_A.col and TABLE_B.col can exist in the TABLE_C.column. IE:

TABLE_A

col
----
a
b
c

TABLE_B

col
----
c

Based on this example data, TABLE_C.column could only ever allow "c" as value to exist in the column if someone added two foreign key constraints to the TABLE_C.column, referencing TABLE_A.col and TABLE_B.col.

Answer 3

First, your example tables are doing too much. There is a design principle that states that a single table should model an entity or a relationship between entities but not both. The relationships between departments, directors and employees (I'm assuming that directors are not employees; I'm also omitting divisions for the moment).

Second, a table can have more than one key, known as candidate keys. Further, you can create a UNIQUE constraint by 'appending' a non-unique column to a key. For example, employees' names do not make for a good key, hence the reason for having an employee ID (I don't think the same can be said for departments i.e. department name in itself is a good enough key). If employee_ID is unique then it follows that (employee_name, employee_ID) will also be unique.

Third, a table can be referenced by any UNIQUE constraint, it doesn't have to be the table's 'primary' key (which partly explains why 'primary key' is a bit of a nonsense).

The great thing about the above is that one can model the required constraints using FOREIGN KEY and row-level CHECK constraints. SQL optimizers and programmers prefer declarative solutions to procedural code (triggers, stored procs, etc). This vanilla SQL DDL will port to most SQL products.

So, the department name can be combined with both the director key and the employee key respectively and these compound keys can be referencesd in a simple two-tier org chart table: because both the employee's department and their director's department will appear in the same table, a simple row-level CHECK constraint can be used to test that they are the same e.g.

Entity tables:

CREATE TABLE Departments
(
 department_name VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE Employees
(
 employee_ID INTEGER NOT NULL UNIQUE, 
 employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE Directors
(
 director_ID INTEGER NOT NULL UNIQUE, 
 director_name VARCHAR(100) NOT NULL
);

Relationship tables:

CREATE TABLE EmployeeDepartments
(
 employee_ID INTEGER NOT NULL UNIQUE
    REFERENCES Employees (employee_ID), 
 employee_department_name VARCHAR(30) NOT NULL 
    REFERENCES Departments (department_name), 
 UNIQUE (employee_department_name, employee_ID)
);

CREATE TABLE DirectorDepartments
(
 director_ID INTEGER NOT NULL UNIQUE
    REFERENCES Directors (director_ID), 
 director_department_name VARCHAR(30) NOT NULL 
    REFERENCES Departments (department_name), 
 UNIQUE (director_department_name, director_ID)
);

CREATE TABLE OrgChart
(
 employee_ID INTEGER NOT NULL UNIQUE, 
 employee_department_name VARCHAR(30) NOT NULL,  
 FOREIGN KEY (employee_department_name, employee_ID)
    REFERENCES EmployeeDepartments 
       (employee_department_name, employee_ID), 
 director_ID INTEGER NOT NULL, 
 director_department_name VARCHAR(30) NOT NULL,  
 FOREIGN KEY (director_department_name, director_ID)
    REFERENCES DirectorDepartments 
       (director_department_name, director_ID), 
 CHECK (employee_department_name = director_department_name)
);

Now a slightly more interesting scenario would be when a director is assigned a division, rather than a specific department, and you had to test that the employee's department was in the same division as her director:

Entity tables:

CREATE TABLE Divisions
(
 division_name VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Departments
(
 department_name VARCHAR(30) NOT NULL UNIQUE, 
 division_name VARCHAR(20) NOT NULL
    REFERENCES Divisions (division_name), 
 UNIQUE (division_name, department_name)
);

CREATE TABLE Employees
(
 employee_ID INTEGER NOT NULL UNIQUE, 
 employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE Directors
(
 director_ID INTEGER NOT NULL UNIQUE, 
 director_name VARCHAR(100) NOT NULL
);

Relationship tables:

CREATE TABLE EmployeeDepartments
(
 employee_ID INTEGER NOT NULL UNIQUE
    REFERENCES Employees (employee_ID), 
 employee_department_name VARCHAR(30) NOT NULL 
    REFERENCES Departments (department_name), 
 UNIQUE (employee_department_name, employee_ID)
);

CREATE TABLE DirectorDivisions
(
 director_ID INTEGER NOT NULL UNIQUE
    REFERENCES directors (director_ID), 
 director_division_name VARCHAR(20) NOT NULL 
    REFERENCES divisions (division_name), 
 UNIQUE (director_division_name, director_ID)
);

CREATE TABLE OrgChart
(
 employee_ID INTEGER NOT NULL UNIQUE, 
 employee_department_name VARCHAR(30) NOT NULL,  
 FOREIGN KEY (employee_department_name, employee_ID)
    REFERENCES EmployeeDepartments 
       (employee_department_name, employee_ID), 
 employee_division_name VARCHAR(20) NOT NULL 
    REFERENCES divisions (division_name),
 FOREIGN KEY (employee_division_name, employee_department_name)
    REFERENCES Departments (division_name, department_name), 
 director_ID INTEGER NOT NULL, 
 director_division_name VARCHAR(20) NOT NULL,  
 FOREIGN KEY (director_division_name, director_ID)
    REFERENCES DirectorDivisions 
       (director_division_name, director_ID), 
 CHECK (employee_division_name = director_division_name)
);