CODEWITHSUNDEEP
mysqlsqlunique-constraint
Joshi
Joshi

Reputation: 2790

How do I specify a composite unique constraint in MySQL

I have a table daily_ward entry with following columns 

daily_ward
-------------
services integer
ipd_patient_id integer
date_time datetime

Now I want to restrict one entry per day for services where services =1.

My date_time field contains time also and services contains many services.

As such I cannot straight away add a unique index.

How should I go about it?

Upvotes: 0

Views: 165

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Oh, this is so sad. You can't do this in MySQL using the data types that you have. Here are three alternatives.

The first is to split the date and time into two columns:

  • date date
  • time time

(You should provide better names.) Then create a unique index on the services and date columns.

The second is to store the date/time in an ISO standard format: "YYYY-MM-DD HH:MM:SS" is usually sufficient:

  • datetime_str char(19)

Then build an index using a prefix:

create unique index idx_daily_ward_services_day on (services, left(datetime_str, 10));

Finally, you could add another column called date and populate it using a trigger.

Given that the name of the table is "daily_ward", I would be inclined to go with the first solution. The "date" seems to be an important part of what the entity represents.

Upvotes: 1

Related Questions