2

check constraint won't work

CREATE TABLE IF NOT EXISTS supervisor (
sup_id INT(3) NOT NULL,
sup_name VARCHAR(30) NOT NULL,
gen VARCHAR(1) NOT NULL CHECK (gen='M' or gen='F'),
dep_id INT(4),

PRIMARY KEY (sup_id),
INDEX (dep_id),

FOREIGN KEY (dep_id)
    REFERENCES department(dep_id)
    ON UPDATE CASCADE ON DELETE RESTRICT    
);

i also tried:

 CONSTRAINT chk_supervisor_gen CHECK ('M' or 'F')

neither of these stopped this information being entered

INSERT
INTO supervisor (sup_id, sup_name, gen, dep_id)
VALUES  
       (1, 'hello', 'G', 1);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
terrible programming
  • 49
  • 7
  • 5
    As far as I know MySQL does not support `CHECK` constraints. The `CHECK` clause is parsed *but* ignored by *all storage engines*. – Giorgos Betsos Jun 04 '15 at 05:56
  • from the StackOverflow Help Center: [**What should I do when someone answers my question?** http://stackoverflow.com/help/someone-answers](http://stackoverflow.com/help/someone-answers). – spencer7593 Jun 04 '15 at 13:57

2 Answers2

3

MySQL doesn't enforce check constraints.

This is a well documented deviation from the SQL standard. (Though it unexpected by the uninitiated.)

If you need the MySQL database to enforce a "check constraint", the enforcement has to be coded into a BEFORE INSERT and a BEFORE UPDATE trigger.

This note:

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

is buried in the MySQL Reference Manual, under the CREATE TABLE syntax.

Reference: https://dev.mysql.com/doc/refman/5.5/en/create-table.html

WARNING ABOUT ENUM

An ENUM does not restrict "invalid" values from being inserted; an invalid value is translated into a zero length string, a warning is issued, but it's not an error.

Demonstration:

CREATE TABLE foo (gen ENUM('M','F'))

INSERT INTO foo (gen) VALUES ('x')

-- Warning Code : 1265
-- Data truncated for column 'gen' at row 1

SELECT gen, CHAR_LENGTH(gen) FROM foo;

-- gen  CHAR_LENGTH(gen)  
-- ---  ----------------
--                     0
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • thank you, now im confused if this is what my teacher wants or not. seems a little to much for us but he would know that we cant use a check constraint. thanks for hlep – terrible programming Jun 04 '15 at 06:07
  • I'd expect your teacher would pleased to find out that you discovered that the `CHECK` clause is accepted, but that it's not enforced by MySQL. He will be pleased that you tested it, and discovered this. If it's a requirement to enforce this in the database, the `BEFORE INSERT` and `BEFORE UPDATE` triggers are the only real mechanism available in MySQL. – spencer7593 Jun 04 '15 at 06:17
  • "In theory, there's no difference between theory and practice. In practice, there is." When you tell your teacher about this discovery, be sure you have a demonstration test case. SQL Fiddle is really handy for this [**SQL Fiddle Demonstration** http://sqlfiddle.com/#!9/27a45/1](http://sqlfiddle.com/#!9/27a45/1) – spencer7593 Jun 04 '15 at 06:23
  • Possibly, the course material was developed for use with Oracle, SQL Server, DB2, Teradata, et al., and it hasn't been updated for MySQL. Or, your teacher is creating an environment in which you are learning how to learn by yourself. Likely, the teacher wants to emphasize the importance of **testing**. – spencer7593 Jun 04 '15 at 06:26
0

Unfortunately MySQL does not support SQL check constraints. So try using enum instead

CREATE TABLE IF NOT EXISTS supervisor (
sup_id INT(3) NOT NULL,
sup_name VARCHAR(30) NOT NULL,
gen enum('M','F') NOT NULL,
dep_id INT(4),

PRIMARY KEY (sup_id),
INDEX (dep_id),

FOREIGN KEY (dep_id)
    REFERENCES department(dep_id)
    ON UPDATE CASCADE ON DELETE RESTRICT    
);
Manashvi Birla
  • 2,837
  • 3
  • 14
  • 28