Foreign key restrictions

Question

I have two tables say, Hotel with Primary Key Hotel_ID and Staff with Primary Key Staff_ID. The Hotel table also has another column, Manager_ID referencing Staff_ID of Staff. The Staff table references Hotel_ID using its column H_ID to indicate which hotel the staff is working at.

I have been trying to figure out if it is possible to restrict allowed values for Manager_ID so that only the staff of that same hotel can be made the manager.

I understand that there is a cross-reference here but my understanding is that it shouldn't be a problem. Could someone tell me how this could be incorporated into the create statement for the Hotel table?

Can you change/edit the table structure to have a third table for the connection between hotels and staff, including the role they have at that hotel? — Progman, Nov 07 '20 at 22:48
No, a staff member can only work at one hotel. And (s)he can become the manager of no other hotel other than the one where (s)he is employed. — dejavo, Nov 07 '20 at 23:01

score 0 · Answer 1 · answered Nov 07 '20 at 23:12

0

You can create a flag or role column in the staff table to indicate if the person is a manager or has a specific role in the hotel. You won't need the Manager_Id column in the hotel table anymore. The tables might look like this:

Hotel
 - Id
 - Name

Staff
 - Id
 - Hotel_Id
 - IsManager
 - Firstname
 - Lastname

When you add a new stuff you can set the IsManager flag to indicate that this person is the manager of the hotel, identified by the id in the Hotel_Id column.

answered Nov 07 '20 at 23:12

Progman

16,827
6
33
48

But wouldn't this increase redundancy? To see who is the manager we will have to do a join to identify that particular entry where `IsManager` value matches "Yes". I was trying to avoid this. – dejavo Nov 07 '20 at 23:18

score 0 · Accepted Answer · answered Nov 07 '20 at 23:43

You can add a multicolumn foreign key to select not just the id of the staff, but also the assigned hotel_id inside your hotel table. This would result in a weird looking table structure like this:

Hotel
 - Id
 - Name
 - Manager_Id
 - Hotel_Id

Staff
 - Id
 - Hotel_Id
 - IsManager
 - Firstname
 - Lastname

This is specially looking weird as you have the same id in the Hotel table twice. And for that to work correctly, you have to add triggers to verify that the Id and Hotel_Id value are the same, when the Hotel_Id column is used. And since you have some kind of circle references (Staff references the Hotel table via Hotel_Id, Hotel references the Staff table via Manager_Id) you have to run ALTER TABLE statements to add the additional columns Manager_Id and Hotel_Id. The queries might look like this:

CREATE TABLE Hotel
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(30)
);

CREATE TABLE Staff
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Hotel_Id INT NOT NULL,
    FirstName VARCHAR(30),
    LastName VARCHAR(30),
    FOREIGN KEY (Hotel_Id) REFERENCES Hotel(Id),
    INDEX (Id, Hotel_Id)
);

The second index is used for the foreign key in the Hotel table:

ALTER TABLE Hotel ADD Manager_ID INT NULL;
ALTER TABLE Hotel ADD Hotel_ID INT NULL;
ALTER TABLE Hotel ADD FOREIGN KEY (Manager_ID, Hotel_ID) REFERENCES Staff(Id, Hotel_Id);

Unfortunately, you cannot use a CHECK CONSTRAINT on the value of the Id column. If you could, it would be possible to write something like:

ALTER TABLE Hotel ADD CONSTRAINT CHK_Hotel CHECK (Hotel_ID = Id OR Hotel_Id IS NULL);

This would verify that the Hotel_Id column, which value come from the Staff table, must be the same as the Id column. However, you will get the following error message:

Check constraint 'CHK_Hotel' cannot refer to an auto-increment column.

You have to add a trigger for INSERT and UPDATE queries (see questions like Constant column value in MySQL table) to make that check.

The following example queries shows how the foreign keys are working:

INSERT INTO Hotel (Name) VALUES ('Some Hotel Name');
SELECT * FROM Hotel;
+----+-----------------+------------+----------+
| Id | Name            | Manager_ID | Hotel_ID |
+----+-----------------+------------+----------+
|  1 | Some Hotel Name |       NULL |     NULL |
+----+-----------------+------------+----------+

INSERT INTO Staff (Hotel_Id, FirstName, LastName) VALUES (1, 'John', 'Doe');
SELECT * FROM Staff;
+----+----------+-----------+----------+
| Id | Hotel_Id | FirstName | LastName |
+----+----------+-----------+----------+
|  1 |        1 | John      | Doe      |
+----+----------+-----------+----------+

UPDATE Hotel SET Manager_Id = 1, Hotel_Id = 1 WHERE Id = 1;
SELECT * FROM Hotel;
+----+-----------------+------------+----------+
| Id | Name            | Manager_ID | Hotel_ID |
+----+-----------------+------------+----------+
|  1 | Some Hotel Name |          1 |        1 |
+----+-----------------+------------+----------+

UPDATE Hotel SET Manager_Id = 1, Hotel_Id = 2 WHERE Id = 1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`Hotel`, CONSTRAINT `Hotel_ibfk_1` FOREIGN KEY (`Manager_ID`, `Hotel_ID`) REFERENCES `Staff` (`Id`, `Hotel_Id`))

Foreign key restrictions

2 Answers2