• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

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')));
0
chonabraham
Asked:
chonabraham
1 Solution
 
arnoldCommented:
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now