Solved

How to use Check Constraints in MySQL

Posted on 2016-09-18
2
28 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 76

Accepted Solution

by:
arnold earned 500 total points
Comment Utility
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 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

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…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now