 |
There are at least two ways to prevent this from happening - perform validation in PHP code or in the database itself using triggers. I believe handling it on the DB level is safer, since it will prevent users from inserting invalid data via other means. Here's how to handle it with triggers: CREATE TRIGGER `checkForDuplicateLinksBeforeInsert` BEFORE INSERT ON `Link` FOR EACH ROW BEGIN DECLARE count INTEGER; IF NEW.porta = NEW.portb THEN SET NEW.porta = NULL; END IF; SELECT COUNT(*) INTO count FROM Link WHERE porta IN (NEW.porta,NEW.portb) OR portb IN (NEW.porta,NEW.portb); IF count > 0 THEN SET NEW.porta = NULL; END IF; END; CREATE TRIGGER `checkForDuplicateLinksBeforeUpdate` BEFORE UPDATE ON `Link` FOR EACH ROW BEGIN DECLARE count INTEGER; IF NEW.porta = NEW.portb THEN SET NEW.porta = NULL; END IF; SELECT COUNT(*) INTO count FROM Link WHERE (NEW.porta IN (porta,portb) AND NEW.porta != porta) OR (NEW.portb IN (porta,portb) AND NEW.portb != portb); IF count > 0 THEN SET NEW.porta = NULL; END IF; END; MySQL does not allow you to raise an exception or return a valid error message by other means, so if the validation check fails, it sets porta to NULL which violates the NOT NULL column constraint. Triggers are supported in MySQL >= 5.0 (I doubt many people still use 4.x). Note that adding these triggers to an existing DB would not affect invalid data that already exists. That must be cleaned up manually. Let me know if you want this added to version 0.19, I'll be glad to do it. |
 |
Yes, that would be great, if you could handle this issue. |
|
|
Fixed in 0.19.0 (SVN commit 4064). |
|
|
Many users run MySQL <5.1.6 which requires the SUPER privilege to use triggers. Triggers were replaced with a SELECT-before-INSERT validation scheme. Perhaps they will be reintroduced someday when most users are running >5.1.6. |
- Anonymous
