timespan primary key

teniosoft · #1 (**permalink**) 03-24-06, 14:28

Hi I would like to make it so that two rows can not exist in the same timespan, interval or between the same period (I really am not quite sure what the correct terminology for this is). Example to appointments can not conflict.

I am using Postgres 8.1 for my database but would like a sollution that would work with all databases (or most). Does anyone know how I could accomplish this.

Stephen

LKBrwn_DBA · #2 (**permalink**) 03-24-06, 14:57

Do as the title of your post says: Create primary key on "patient_id"+"appointment date/time"

teniosoft · #3 (**permalink**) 03-24-06, 16:23

This does not handle a span of time you could have an appointment for 30 minutes but the database would still allow you to insert an appointment at 12:30 and then 12:31 and so on and so forth.

andrewst · #4 (**permalink**) 03-27-06, 08:13

If your DBMS supports the ANSI "CREATE ASSERTION" syntax then you could use that. It would be something like:

Code:

CREATE ASSERTION x CHECK
NOT EXISTS
( SELECT NULL FROM appointments a, appointments b
  WHERE a.patient_id = b.patient_id
  AND a.start <= b.end
  AND b.start <= a.end
);

However, Oracle for one DBMS does not support assertions, and you would have to use other methods e.g. triggers or materialized views with constraints.

Internet Services

Web Development

Digital Marketing

Consumer Tech