16

I have a column called "Patient Type" in a table. I want to make sure that only 2 values can be inserted in to the column , either opd or admitted, other than that, all other inputs are not valid.

Below is an example of what I want

enter image description here

How do I make sure that the column only accepts "opd" or "admitted" as the data for "Patient Type" column.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Troller
  • 1,108
  • 8
  • 29
  • 47

3 Answers3

16

I'm not a MySQL dev, but I think this might be what you're looking for. ENUM

MarkD
  • 5,276
  • 1
  • 14
  • 22
  • 3
    Yes, ENUM is a solution that works for this specific problem and far easier than a CHECK constraint. Especially as CHECK doesn't work in MySQL and you need a trigger. An ENUM also is far easier to understand, so it better documents the constraint of the column. – Christopher K. Oct 17 '16 at 08:13
  • 1
    You might not be a MySQL dev but you gave much better solution than one accepted. And here is why, ENUM type stores all possible values in table description and not in table data, ENUM stands for enumerated and all is stored is number 1 2 3 and so on when enumerated is used. Where if constraints are used data is actually stored. Constraints make sense when you have dynamic constraints, in here NO. –  Nov 28 '17 at 22:13
12

You need a check constraint.

ALTER TABLE [TableName] ADD CONSTRAINT 
my_constraint CHECK (PatientType = 'Admitted' OR PatientType = 'OPD')

You need to check if it works though in MySQL in particular as of today.
Seems it does not work (or at least it did not a few years ago).

MySQL CHECK Constraint

CHECK constraint in MySQL is not working

MySQL CHECK Constraint Workaround

Not sure if it's fixed now.

If not working, use a trigger instead of a check constraint.

Community
  • 1
  • 1
peter.petrov
  • 38,363
  • 16
  • 94
  • 159
  • 1
    The [manual](http://dev.mysql.com/doc/refman/5.7/en/create-table.html) of MySQL 5.7 still says: "The CHECK clause is parsed but ignored by all storage engines." – Christopher K. Nov 16 '14 at 18:13
  • I think this is not a good solution check my comment to answer by @MarkD –  Nov 28 '17 at 22:05
  • According to the ticket filed for this, CHECK was fixed in MySQL 8.0.15 – cjn Nov 25 '20 at 20:04
3

While creating the table use Enum as data type for patientType column. Create table [tablename](firstname varchar(size),[othercolumns],patientType ENUM('OPD','Admitted'))

Ubi
  • 104
  • 10