1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

create query problem (use of check constraint)

Discussion in 'SQL PL/SQL' started by crackthehit007, Apr 28, 2009.

  1. crackthehit007

    crackthehit007 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Code (SQL):
    CREATE TABLE x
        (   aa NUMBER(2),
            aaa VARCHAR2(22)
            CONSTRAINT hi CHECK
            (
                (   SELECT COUNT (1)
                    FROM x
                    WHERE aaa = '1'
                 ) < 2
             )
        );

    ERROR::subquery NOT allowed error
    i have a table x having 4 columns and out of it in 3rd column name is flag.......its value can be only true or false. the column can have n number of entries for true but can have only 1 false value.

    i am trying to achieve same using this but i am not getting... :( is there any other way to form this query?
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    i am not sure i understand your requirement properly. Is it that you have a column in which the value "False" can appear only once. If that is the case you can't stop it from a check constraint as oracle does not allow subqueries within check constraints. You can either use triggers (before insert and before update) or you can consider changing your design to have primary key-foreign key relations between two tables.
     
  3. crackthehit007

    crackthehit007 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    yes exactly...
    i dont want to go for either of your solution..is there any other way to achieve it?
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    well i am afraid not... since u can't use sub-queries within check constraints in oracle
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is no method to check this constraint at table level . Trigger will be the better method to do it. OR can be done at procedure level also.

    You may be able to do in Oracle 11g using Virtual Column .

    Virtual Column
     
  6. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    what i think is that trigger level is the better option for this.........