Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    6

    Unanswered: Is this constraint possible?

    Hi all,

    apologies for my pseudo code sql below, but is something like I've described below possible?

    My sql isn't quite there yet hence why some syntax is there and some isn't, that's the bit I need your help with....

    ALTER TABLE
    example
    ADD CONSTRAINT
    check_display_seq_id_unique
    CHECK (
    inserted-display_seq_number (the one being used in the insert) not in (select display_seq_number
    from example
    where service_group_id = inserted-service_group_id(the one being used in the insert)
    and service_package_id = inserted-service_package_id(the one being used in the insert))
    )


    Thanks in advance
    Last edited by greyfloppyhat; 08-23-12 at 07:58.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Nope. This type of thing can only be done via trigger. <--- rubbish, wasn't paying attention to the pseudo code
    Last edited by dayneo; 08-23-12 at 08:17. Reason: Me being dumb-ass

  3. #3
    Join Date
    Aug 2012
    Posts
    6
    Thanks for the speedy reply Dayneo. How could I achieve this through a trigger?

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Maybe you should realize that there are more constraint types than the CHECK one.

    At the first look, your pseudocode looks like UNIQUE constraint on all involved columns (SERVICE_GROUP_ID, SERVICE_PACKAGE_ID, DISPLAY_SEQ_NUMBER).

    For correct syntax, just consult SQL Language Reference book for your Oracle version. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Wink

    Sorry, wasn't thinking about that one. Use Unique constraint. Here's example:
    Code:
    dayneo@RMSD> create table example_tbl (
      2    service_group_id   number,
      3    service_package_id number,
      4    display_seq_number number
      5  )
      6  /
    
    Table created.
    
    dayneo@RMSD> CREATE UNIQUE INDEX exampe_tbl_uk1 ON example_tbl
      2  (service_group_id, service_package_id, display_seq_number)
      3  /
    
    Index created.
    
    dayneo@RMSD> insert into example_tbl values(1, 1, 1);
    
    1 row created.
    
    dayneo@RMSD> insert into example_tbl values(1, 1, 2);
    
    1 row created.
    
    dayneo@RMSD> insert into example_tbl values(1, 1, 3);
    
    1 row created.
    
    dayneo@RMSD> insert into example_tbl values(1, 1, 1);
    insert into example_tbl values(1, 1, 1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (DAYNEO.EXAMPE_TBL_UK1) violated

Posting Permissions

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