is it possible to add a condition to foreign key constrain in phpmyadmin?

Question

I have two tables: Message and Product. In the Message table, there is a sourceid, which is equal to ProductId under table Product.

A Product is given a product id (productID), and a user can message the seller regarding the product. And for those messages, if they are related to certain product than they are given a sourceid, which is basically the product id. If the message is not related to any product, the sourceid will be set as 0.

I am able to set a foreign key constrain to delete the message entry whenever a seller delete a product, all related messages will be deleted.

However, I found that all message that is not related to a product, with sourceid set as 0, are not saving in the database. Obviously, because there is not a product with a productid = 0, and that the foreign key constraint will delete the message entry (with sourceid = 0) immediately.

Is there a way to set condition in phpmyadmin? If I can set that if the foreign key constraint only execute when sourceid > 0, then the system should work out. But I am not sure how to set this condition, or if phpmyadmin allow to set this condition.

Please help. Thank you so much.

score 2 · Answer 1 · answered Jun 28 '17 at 04:24

2

Yes, if the foreign key is not relevant to some messages, make the product_id foreign key column be nullable. Store NULL instead of 0 when you don't want to reference any product. The NULL is ignored for purposes of the foreign key reference.

This is virtually the same as these past questions I answered:

MySQL foreign key to allow NULL? (2009)
Foreign key or null value (2011)

answered Jun 28 '17 at 04:24

Bill Karwin

538,548
86
673
828

So all I have to do is set the sourceid to null instead of 0 whenever referring to messages that are not related to product? – Kevin Jun 28 '17 at 04:32
If virtually the same, you could close this question as a duplicate. I think the OP is actually asking multiple things. For some reason, I gave an answer about `ON CASCADE DELETE` :-) – Tim Biegeleisen Jun 28 '17 at 04:32
I was thinking another way to workaround, is it possible to set in phpmyadmin that when the product is deleted, related message entry will update its sourceid to 0? – Kevin Jun 28 '17 at 04:49
@Kevin, MySQL supports `ON DELETE SET NULL` but it does not support `ON DELETE DEFAULT`. You should read https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html – Bill Karwin Jun 28 '17 at 16:14
@TimBiegeleisen, I should say the *answer* is virtually the same, even if this question is different enough that I decided not to vote to close it as a duplicate. – Bill Karwin Jun 28 '17 at 17:15

score 0 · Answer 2 · answered Jun 28 '17 at 04:16

You can define the Message table with a foreign key using ON DELETE CASCADE:

CREATE TABLE Message (
    messageid INT(11) NOT NULL AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL,
    -- other columns
    sourceid INT(11) NOT NULL,
    PRIMARY KEY (messageid),
    FOREIGN KEY (sourceid) REFERENCES Product(ProductId) ON DELETE CASCADE
) ENGINE=InnoDB;

If you don't want to redefine the entire table, you can use the following ALTER TABLE statement:

ALTER TABLE Message
ADD CONSTRAINT fk_pid
FOREIGN KEY (sourceid)
REFERENCES Product (ProductId)
ON DELETE CASCADE

With this change in place, deleting a product record from the Product table will cause all its corresponding messages in Message to also be deleted.

So all I have to do is set the sourceid to null whenever refering to messages that is not related to product instead of 0? — Kevin, Jun 28 '17 at 04:31
@Kevin I don't know what you have in mind, but going by Bill Karwin's answer, if you set `ProductId` to `NULL`, and then delete that record, it should ignore the cascade delete of the corresponding messages. — Tim Biegeleisen, Jun 28 '17 at 04:35

is it possible to add a condition to foreign key constrain in phpmyadmin?

2 Answers2