I use MySQL 5.6 and I have 3 tables. Two of them are a list of data, and the third table is used for a relationship between them. In the third table I would like to identify a default record for each id1, so I added a column named predt to let me know which record is the default one. Something like this:
id1 | id2 | predt 1 | 1 | 0 1 | 2 | 1 1 | 3 | 0
In this example I will know that the second row is the default one for id1 = 1.
Whats worries me is that it’s possible that more than one record could have the value 1 for column predt where id1 = 1.
I validate that this doesn’t happen, is the first thing I do in the SP that inserts or updates a record in this table. But if someone updates the table directly there is no restriction in the table to stop it from doing so (like a foreign key restriction).
create table table1 ( id int(10) not null auto_increment, description varchar(100) not null, primary key(id)) engine innodb, default character set latin1; create table table2 ( id int(10) not null auto_increment, description varchar(100) not null, primary key(id)) engine innodb, default character set latin1; create table table3 ( id1 int(10) not null, id2 int(10) not null, predt tinyint(1) not null default 0, primary key(id1,id2)) engine innodb, default character set latin1;
I always treated this case in the same way, and so far it has worked. But I would like to know if there is a better way to work with this kind of cases.
Advertisement
Answer
You should test if for that particular row already exists such predt = 1 In higher mysql Versions you could use a CHECK Constraint with the same purpose
DELIMITER $$ CREATE TRIGGER before_insert_table3 BEFORE INSERT ON table3 FOR EACH ROW BEGIN IF NEW.predt = 1 then IF EXISTS(SELECT 1 FROM table3 WHERE predt = 1 AND id1 = NEW.id1) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = ' predt 1 already exist'; END IF; END IF; END $$ DELIMITER ; DELIMITER $$ CREATE TRIGGER before_update_table3 BEFORE UPDATE ON table3 FOR EACH ROW BEGIN IF NEW.predt = 1 then IF EXISTS(SELECT 1 FROM table3 WHERE predt = 1 AND id1 = NEW.id1) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = ' predt 1 already exist'; END IF; END IF; END $$ DELIMITER ;