Restricting a column to accept only 2 values

Question

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

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

Answer 1

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.

Answer 2

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

Answer 3

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