Solved

How to use Check Constraints in MySQL

Posted on 2016-09-18
2
43 Views
Last Modified: 2016-10-03
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
Comment
Question by:chonabraham
2 Comments
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 41804010
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 41804501
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question