0

I have a table "comments", containing a basic auto-inc PK "id" field, and also a "type" field. At another tables, I want to have referential integrity applying a CONSTRAINT to comments.id AND ALSO the "type", using a fixed value for the type value.

Example: at table "question_comments_rate", I want to enforce "comment_id" to exist as field "id" in "comments" table [this is the easy part] AND ALSO that the "type" column is equal to "question".

Does this exist? How can I write this constraint declaration?

It is like a "conditional constraint" - not only "comment_id" field must exist as "comments.id", but also this "id" at "comments" table needs to be of a row where "type" is equal to "question" (or other specified value)...

I hope the problem is clear. I may make it clearer with your help/feedback. Thanks.

J. Bruni
  • 20,322
  • 12
  • 75
  • 92

1 Answers1

3

This would be a CHECK constraint, about which the MySQL manual says:

The CHECK clause is parsed but ignored by all storage engines.

You could instead create triggers on insert/update to enforce your sanity checks.

Or, in your case, you could make the data type of your type column ENUM('question') so that it cannot take any other value (except '', which all ENUM types can be - for example if they are set to an invalid value).

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Thanks, eggyal. I've seen (and upvoted) your answer as soon as it appeared... I'm waiting to see if someone has another idea, but it seems it is just not possible... My "type" column must be able to take other values, not only "question"... It seems I will be doing this check in the PHP layer - which is the server-side language I'm using in this case. – J. Bruni May 01 '12 at 12:04
  • If the values that `type` can hold are enumerable (that is, you can list them all), you can (indeed, *should*) still use `ENUM`: e.g. `ENUM('question','answer','comment','wibble');`. – eggyal May 01 '12 at 12:08
  • I am using ENUM. I didn't mention this because there is people around who thinks that ENUMs are evil... http://www.google.com.br/search?q=enum+is+evil – J. Bruni May 03 '12 at 12:51