Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: Unique constraint including two dates

    I have a table:

    number1 number;
    number2 number;
    start date;
    end date;

    I created a unique constraint on the table:
    unique(number1, number2, start, end)

    I have two records in the table:

    number1 10
    number2 20
    start null
    end Nov. 15, 2004

    number1 10
    number2 20
    start null
    end null

    and I try to update the second record with any end date different from that of the first record, I get a violation of the unique constraint. The end dates are different so I would expect to be fine but I can't seem to update the second record with any end date. Why is this and how can I accomplish what I need?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It works for me:
    Code:
      1  create table t1 (number1 number,
      2  number2 number,
      3  startd date,
      4  endd date,
      5* unique(number1,number2,startd,endd))
    SQL> /
    
    Table created.
    
    SQL> insert into t1 values (10,20,null,'15-nov-2004');
    
    1 row created.
    
    SQL> insert into t1 values (10,20,null,null);
    
    1 row created.
    
    SQL> update t1 set endd='16-nov-2004'
      2  where endd is null;
    
    1 row updated.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by bernsch
    I have a table:

    number1 number;
    number2 number;
    start date;
    end date;

    I created a unique constraint on the table:
    unique(number1, number2, start, end)

    I have two records in the table:

    number1 10
    number2 20
    start null
    end Nov. 15, 2004

    number1 10
    number2 20
    start null
    end null

    and I try to update the second record with any end date different from that of the first record, I get a violation of the unique constraint. The end dates are different so I would expect to be fine but I can't seem to update the second record with any end date. Why is this and how can I accomplish what I need?
    It is a vary bad idea to use reserved words for column names. (START, END). And if you need help, please show the exact update command that failed. We can't diagonose a problem with no information.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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