Results 1 to 5 of 5

Thread: Uniqe Index

  1. #1
    Join Date
    Jun 2003
    Posts
    58

    Question Unanswered: Uniqe Index

    Hi,
    I want to create a table that contains a few fields, but two of them are supposed to be equal (at their role) and those two are the primary key.
    The table is supposed to contain those fields: AGREEMENT1, AGREEMENT2, TYPE, DESCRIPTION.
    The primary key is built from the 2 agreement fields. My problem is that the 2 following rows are equal by me, and I don’t want the possibility of the two of them exist at the same time:
    Agreement1 Agreement2
    1 2
    2 1
    A unique index on Agreement1, Agreement2 won't be enough here.
    Is there any way to do that thing?

    Thanks, Ma'ayan.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Try using trigger before insert.

    The code should be something like:
    CREATE TRIGGER trigger_name before insert ON table_name REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.A, N.B IN (SELECT B, A FROM table_name)) SIGNAL SQLSTATE '75000' ('Error: Agreements are the same')

    Try repaire the above code to work for you.

    You will probably have to write the "before update" trigger also.


    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Jun 2003
    Posts
    58
    Thanks...
    Is there a way to make it without triggers ?

  4. #4
    Join Date
    Jul 2003
    Posts
    4
    Maayan!
    Don't be afraid of trigers! They are your friends.
    And you can insert this logic into application (which is a perfectly good solution).

    Originally posted by maayanp
    Thanks...
    Is there a way to make it without triggers ?

  5. #5
    Join Date
    Jun 2003
    Posts
    58

    Talking


Posting Permissions

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