Solved

How to use Check Constraints in MySQL

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
[MYSQL]: Delete is very slow 4 91
MySql hide the stored procedures 2 54
AWS EC2 & RDS Instance 5 50
Showing random records from database 10 44
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

792 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