CODEWITHSUNDEEP
mysql
social_loser
social_loser

Reputation: 153

mysql is it possible to define a SET type foreign key based on other tables value?

There are table1 and table2, currently there is a foreign key in table1:

FOREIGN KEY (foreign_ref) REFERENCES `table2` (chars)
    ON UPDATE CASCADE,

is it possible to make foreign_ref of type SET, and make the possible set values from table2.chars? and even better, to make foreign_ref auto add possible values when insert new rows into table2?

update:

Actually , the scenario I wish to apply is that table1 is a item table, table2 is a tags table. but 1 item in table1 can have multiple tags. table2 is not going to contain a lot of rows but maybe modified frequently, I maybe need to add more tags to be availiable to be attached to future items. adding row to table2 means the new tags is availiable for future items to use. not modifying the exist one.

to be simple, I wish a column in table1 can have varies numbers of tags, and make constrain on it to only allow it to pick from fixed set of values.

Upvotes: 0

Views: 719

Answers (1)

Shadow
Shadow

Reputation: 34285

is it possible to make foreign_ref of type SET

Yes, technically it is possible, but there is no real point in doing so. See the below points for the why.

make the possible set values from table2.chars

No, not possible, the list of values for a set is defined in the table header and can only be changed via alter table statement.

make foreign_ref auto add possible values when insert new rows into table2

No, the foreign key cannot add new values to a set list of values. Again, you need to use an alter table to change the list of values.

Overall: the set data type is best used if there is a relatively small list of values a field may have and that list is not likely to change because you can avoid adding another lookup table and foreign keys. A good example could be the cardinal directions (north, west, etc): you have 4 of them and that list is not going to change. If you need to dynamically edit the list of values that a field may have, then better use a lookup table and foreign keys.

Upvotes: 1

Related Questions