CODEWITHSUNDEEP
postgresqlconstraintscheck-constraints
levelone
levelone

Reputation: 2399

Add CHECK constraint with column from another table

I have two tables:

schedules:

  • id INT
  • daily_schedule_id INT
  • start TIMESTAMP
  • finish TIMESTAMP

daily_schedules:

  • id INT
  • date DATE

I'd like to add a constraint to schedules.start and schedules.finish which checks the value of daily_schedules.date. Is there an easy way to achieve this?

Something along these lines:

ALTER TABLE schedules 
   ADD CONSTRAINT schedules_date_range_check CHECK (start = daily_schedules.date AND finish = daily_schedules.date)

Upvotes: 0

Views: 215

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248295

While there are ways to do that technically, it is not supported and a bad idea, as it may cause dumps that cannot be restored.

It is always a good idea to avoid redundancy in databases, so I recommend that you store start and finish in schedules with the data type time, which is understood relative to the date of the related daily_schedules.

Just add the time and the date, and voila, you got a timestamp.

Upvotes: 1

Related Questions