How to use Check Constraints in MySQL

This still allows other values to be entered from the doc_pri_spec.  How can I use it or what is the syntax for a trigger for the last constraint?

CREATE TABLE doctor (
  doc_id  INTEGER AUTO_INCREMENT,
  doc_fname  VARCHAR (20) NOT NULL,
  doc_mi       VARCHAR (2),
  doc_lname  VARCHAR (20) NOT NULL,
 doc_pri_spec VARCHAR (10),
 doc_street     VARCHAR (20),
 doc_city       VARCHAR (20),
 doc_state     CHAR (2),
 doc_zip       CHAR (5),
 doc_off_ph CHAR (10),
 doc_hm_ph CHAR (10),
 doc_cell_ph CHAR (10),  
     CONSTRAINT doc_id_pk PRIMARY KEY (doc_id),
     CONSTRAINT doc_pri_spec_ck CHECK  (doc_pri_spec  IN ('GP', 'ENT', 'GYN', 'PED')));
chonabrahamAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
arnoldConnect With a Mentor Commented:
You need to provide a data example with an insert that passes
why not define the doc_pri_spec as a set ('GP','ENT','GYN','PED') and be done with it, how are you presenting the data on the entry side?

Alternatively, depending on whether these items need further definition, have a table of their own with the doc_pri_spec be a foreign key to the ID column from the other table. Could a doctor be multi-discipline? in your setup, they can not.

It sounds as though the row will be entered in the current definition

http://www.w3resource.com/mysql/creating-table-advance/constraint.php

try:
alter table doctor modify doc_pri_spec VARCHAR(10) NOT NULL CHECK (doc_pri_spec IN ('GP','ENT','GYN','PED');

...
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
0
 
Tomas Helgi JohannssonCommented:
Hi!

CHECK constraints are not supported by MySQL. You can define them, but they do nothing (as of MySQL 5.7).

From the manual:
    The CHECK clause is parsed but ignored by all storage engines.

The workaround would be to use triggers but that could be a little tricky.

Regards,
     Tomas Helgi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.