CODEWITHSUNDEEP
mysqldatabasedatabase-design
Alex
Alex

Reputation: 68492

CONSTRAINT based on field value

So I have this table

CREATE TABLE meta (
    id BIGINT(20) NOT NULL,
    type VARCHAR(6) NOT NULL,  
    name VARCHAR(64) NOT NULL,
    value LONGTEXT NOT NULL,
        PRIMARY KEY(id, type, name),

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

the type field can only have 3 possible values: "article", "user" or "page". And I have 3 tables for each of those content types.

How can I add a constraint that tells mysql to remove the "meta" row if the type is "article" and id matches a article id form the articles table? And two more contraints for the "user" and "page" types.

Right now I have:

CONSTRAINT article_meta FOREIGN KEY(id)
    REFERENCES articles(id) ON DELETE CASCADE ON UPDATE RESTRICT

but it doesn't take in consideration the "type" field, so the row gets deleted regardless of the type value...

Upvotes: 1

Views: 472

Answers (1)

ruakh
ruakh

Reputation: 183301

I don't think this is possible. If you look at the syntax for a CREATE TABLE statement, foreign keys have to have this form:

[CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...)
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
      [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

So you can't (say) create a foreign key on WHEN type = '...' THEN id ELSE NULL END (because the foreign key has to be on a column-name, or multiple column-names, not arbitrary expressions), and you can't (say) describe a foreign key as only applying WHEN type = '...' (because there's no syntax for that).

But I wonder — are you sure it's a good idea to have a single meta table with metadata for all of these different tables? If the tables have enough in common, semantically, that it makes sense for them to share a meta, then they should also probably share some sort of parent table, entity, that's responsible for their overall IDs (in which case you can use multi-column foreign keys to ensure that types match and everything); but if, as I suspect, they really have nothing in common semantically, then they shouldn't have a single shared meta table, but rather, there should be an article_meta table, a user_meta table, and a page_meta table. No?

Upvotes: 2

Related Questions