2/17/2024 0 Comments Mysql create table with unique key![]() ![]() ![]() The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.Ī FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. ![]() CREATE TABLE ` book_to_author ` ( `author_id` integer, `book_id` integer, PRIMARY KEY ( `book_id`, `author_id` ) ) Foreign key This would likely cause confusion for other users as they would see that they had provided a non-null value and might not realize a trigger was making it null.Import ). The error message you would get would be: ERROR 1048 (23000): Column 'employee_id' cannot be null If you don't want to use signal for your triggers, you could achieve the same effect by setting the value to null as shown below, but this would create confusing error messages to the user. INSERT INTO `terminated_employee` (`employee_id`, `termination_date`) VALUES (1, '') If we want to fire John, we need to remove him from the active table before inserting him into the terminated table with the commands below: START TRANSACTION ĭELETE FROM `active_employee` WHERE `employee_id`=1 You will be shown the following error: ERROR 1644 (45000): Unhandled user-defined exception condition INSERT into terminated_employee (`employee_id`, `termination_date`) VALUES (1, '') Īfter the last command, our triggers will kick in to protect the database state. # Erroneous attempt to insert the same employee into the terminated table. INSERT into `active_employee` (`employee_id`) VALUES (1) INSERT INTO `employee` (`name`) VALUES ("john") Now lets test this by creating an employee called John, and try to add him to both the active and terminated tables. SELECT COUNT(*) INTO c FROM active_employee WHERE employee_id = NEW.employee_id DELIMITER $$ĬREATE TRIGGER unique_terminated_employee BEFORE INSERT ON terminated_employee This trigger will prevent an employee being inserted into the terminated table if they are already in the active one. SELECT COUNT(*) INTO c FROM terminated_employee WHERE employee_id = NEW.employee_id DELIMITER $$ĬREATE TRIGGER unique_active_employee BEFORE INSERT ON active_employee This trigger will prevent an employee being inserted into the active table if they are already in the terminated one. Now how do I prevent the same employee being referenced in both the active and terminated tables? We can use triggers to prevent insertions on either table if the value is in the other. `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,įOREIGN KEY (employee_id) REFERENCES employee(id) ON DELETE RESTRICT ON UPDATE CASCADEįOREIGN KEY (employee_id) REFERENCES employee(id) ON DELETE CASCADE ON UPDATE CASCADE `id` int unsigned NOT NULL AUTO_INCREMENT, You can use the commands below to set up the tables as shown above. However this structure is in preparation for other relationships in future that are beyond the scope of this tutorial. One could just use a single employees table with a nullable termination_date to identify whether an employee is active or terminated for this scenario. This is shown in the example below where I want an employee to be either in the ActiveEmployee table, or the TerminatedEmployee table, but never both. As part of another problem, I wanted to see if it was possible to ensure that a key was only in one of two tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |