"Not include" Constraint?

Question

Thanks for reading this.

I have tables like this in MySQL:

Device table has a list of name. And ReservedName table has a list of "reserved" name list.

As you may see, my design concept is to make Name value of Device table SHOULD NOT one of Name in ReservedName.

I could easily implement this relation by add a few SQL statement when I do INSERT operation to Device table. But I am wondering if there is something like "Not one of" constraint in the table schema? Maybe opposite meaning of FOREIGN KEY? It is also welcome if there is any other way to make that relationship.

you're looking for a check constraint. http://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint may be useful — Kritner, Aug 24 '16 at 15:29

score 0 · Answer 1 · answered Nov 21 '16 at 06:40

You can create a BEFORE INSERT triggers which can either cause an error or set the field to its default value when the requirements of the data are not met.

In your case you can create a trigger, which will raise error, if your validation fails, something like following:

CREATE TRIGGER `validate_before_insert` BEFORE INSERT ON `Device`
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT* FROM ReservedName WHERE Name = new.Name) THEN
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT := 'check constraint on Device.Name failed';
    END IF;
END

You can read more about MySQL triggeres in documentation.

"Not include" Constraint?

1 Answers1