Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    27

    Unanswered: Unique Key Constraints

    Is it possible for me to implement a unique key constraint using a trigger? That every row that would be inputted into the table would be checked if that certain column is already present in the table, if it is then it would be rejected.

  2. #2
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    Why can't you just implement a unique contraint on the table instead???
    if the column is defined as not null then:
    alter table t2 add constraint c1 unique(col-name)

    in case it is not defined as not null.
    a. Then use export utility to export the the data.
    b. Drop the table and recreate it using the not null constraint for the column.
    c. alter table t2 add constraint c1 unique(col-name)
    d. use import/load utlity to get data back into table.

    Regards,
    Sumeet

  3. #3
    Join Date
    May 2002
    Posts
    27
    what happens after i insert a duplicate item to the table? would it reject it or cause an error? what i want to happen is for db2 to trap the error so it won't cause any error message when a duplicate item is inserted into the table, is that possible?

  4. #4
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    The insertion is rejected. I just tried it and it generated a SQL803N error. But if you are trying multiple insertions(using db2 -tvf ) then only the rows violating the constraints are rejected, all others are inserted.

    I also tried creating a trigger to implement the unique constraint .

    db2 create trigger trig1 no cascade before insert on t13 referencing NEW as n1 for each row mode db2sql when (exists(Select 1 from t13 where a=n1.a)) begin atomic signal sqlstate '70001' ('insertion error'); end

    It does the same thing as the unique constraint does. Rejects violating rows, inserts all others.

    Hope this helps.

    Regards
    Sumeet

  5. #5
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    Incase you want to reject the NULL values also,create 2 'BEFORE INSERT' triggers.

    db2 create trigger trig5 no cascade before insert on t13 referencing NEW as n1 for each row mode db2sql when ( n1.a is null ) begin atomic signal sqlstate ' 70001' ('value is null'); end

    db2 create trigger trig2 no cascade before insert on t13 referencing NEW as n1 for each row mode db2sql when (exists(Select 1 from t13 where a=n1.a)) begin atomic signal sqlstate '70002' ('Duplicate key'); end

    Regards
    Sumeet

  6. #6
    Join Date
    May 2002
    Posts
    27
    hey tnx. well it worked out well....is it possible to produce a warning instead of an sqlstate error? and is it possible that if the item is already present that i would then produce an update statement?

  7. #7
    Join Date
    May 2002
    Posts
    27
    Or is it possible that if a duplicate error would occur then it would reject the row and do nothing, i need it to produce no error message or produce just a warning message so that it won't annoy the user using the application is that possible?

  8. #8
    Join Date
    May 2002
    Posts
    27
    sorry..i tried inserting multiple rows but it seems that it rejected the whole thing? what may have been wrong?

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Just my thought ...

    may be commit intervel ....

    Try issuing a commit for each statement ..


    Originally posted by ken_ken
    sorry..i tried inserting multiple rows but it seems that it rejected the whole thing? what may have been wrong?

  10. #10
    Join Date
    May 2002
    Posts
    27
    Has anyone of you guys tried using Warehouse Manager which comes with DB2 UDB or Visual Warehouse? Well i tried using that trigger over that application and it keeps popping that error message and the load or insert to the table is always stopped.

Posting Permissions

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