Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    Portland, Or
    Posts
    21

    Unanswered: timespan primary key

    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
    Stephen Rich
    http://www.teniosoft.com
    Web Applications, Client Server Databases for Linux and Windows

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


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



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2003
    Location
    Portland, Or
    Posts
    21
    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.
    Stephen Rich
    http://www.teniosoft.com
    Web Applications, Client Server Databases for Linux and Windows

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •