CODEWITHSUNDEEP
mysqlcheck-constraints
shahjapan
shahjapan

Reputation: 14335

how to give check constraint on referenced table of foreign key, mysql?

Table: User

     uid = Integer

     superuser = Boolean
Table: Group

     gid = Integer

     grpname = String


Table: User_Group_Rel 

       uid: ForeigneKey(User)

       gid: ForeignKey(Group) 

       CONSTRAINT check1 CHECK (uid.superuser != False),

when I insert data into relational table its not raising exception of constraint

Upvotes: 0

Views: 339

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

Unfortunately, MySQL does not enforce check constraints. It parses check constraint declarations but silently ignores them. It doesn't even generate a warning that it's doing so.

See http://dev.mysql.com/doc/refman/5.1/en/create-table.html:

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

People use a variety of workarounds to constraint columns, for example:

  • Declare the column as an ENUM that has only the values you want to allow.

  • Define a FOREIGN KEY to make the column reference a lookup table that contains only values you want to allow.

  • Write a TRIGGER on BEFORE INSERT and BEFORE UPDATE to change the value or raise an exception (this requires further trickery before MySQL 5.5).

Upvotes: 2

Related Questions