How can I check if the foreign key object is of a certain type

Question

This question is simplified to reduce clutter.

In my database I have 2 tables. 1 table "product" and 1 table "notification". This product can be of 3 types, defined by an enum ( ENUM('SERVER', 'SERVICE', 'APPLICATION') ).

Notification contains a ForeignKey of product, its ID.

When I create a notification I would like to check if the given ForeignKey is of the type 'APPLICATION'. Am I able to perform such a check? If so, is this done by a CHECK?

As far as I know, the only thing I could CHECK is an value (e.g. CHECK(foo > 0) )

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

This easiest thing to do is a Trigger Before Insert and a second one Before Update.

You can make a SELECT of your foreigner key value to test it in the trigger, and release an error.

Take a look at the 2nd answer of CHECK constraint in MySQL is not working

Like this :

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
    IF CHAR_LENGTH( NEW.ID ) < 4 THEN
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT := 'check constraint on Test.ID failed';
    END IF;
END$$   
DELIMITER ;

edited May 23 '17 at 11:50

Community

1
1

answered Feb 09 '16 at 11:50

Blag

5,818
2
22
45

For now, this seems a good solution. If no other answer will be posted, this will be my way to go. But another option, implementing a CHECK or CONSTRAINT in the CREATE TABLE, is no viable option? – creulcat Feb 09 '16 at 11:57
This seems like your best bet imo – Anton Feb 09 '16 at 11:57
1

@creulcat as far as I know MySql, there is no other way to do a distant constraint check than with a trigger... – Blag Feb 09 '16 at 11:58

Anton · Answer 2 · 2016-02-09T11:46:21.980

0

If its an enum, you should be able to check if the value is equal to your desired result.

Join your tables and then use a where clause to filter out the results you dont want. You did not provide the full details of your table so I will give a simple example of what you want to do. My enum is the column "type".

In a simplified form:

            SELECT * 
            FROM products 
            WHERE type = 'APPLICATION'

edited Feb 09 '16 at 11:46

answered Feb 09 '16 at 11:43

Anton

422
2
9

This is indeed possible and is my current solution, but I was wondering if it was possible to add an extra level of safety into the database itself instead of in the `INSERT` statement. – creulcat Feb 09 '16 at 11:46
Can you clarify what you mean? specifically, what is it you want to do? – Anton Feb 09 '16 at 11:47
A very simple example: `CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );` In this example, the database makes sure the price is always `> 0` What I'm trying to achieve is a same sort of check, where the database makes sure the foreign key always is from type `APPLICATION` – creulcat Feb 09 '16 at 11:52

How can I check if the foreign key object is of a certain type

2 Answers2