Check Constraint
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Check Constraint

  1. #1
    Join Date
    Feb 2001
    Posts
    17

    Question

    Hi,

    I have a problem concerning check constraints. In the following table,

    SeqID | Sequence
    ------------------------
    1 | AAGTTTCCCC
    2 | CCCGTTGGCCAAAA

    the column Sequence can only contain strings of the four upper case letters A, C, G and T. Is it possible to defne a check constraint on the table to enforce this? Or do I have to use a trigger?
    --Thanks for your help,

    Dan1


  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    You need a parse function in a trigger to enforce this.

  3. #3
    Join Date
    Apr 2001
    Posts
    118
    Depending upon the nature of the data in that column, you can play around with some of the character functions in your check constraint to achieve the result that you want.

    For example, if you can assume that that field will only contain upper case letters (A-Z), then you can define a check constraint like the following:


        create table <table>(
            SeqID    NUMBER,
            Sequence VARCHAR2(200)
                constraint <name>(
                   INSTR(NVL(TRANSLATE( Sequence, 'BDEFHIJKLMNOPQRSUVWXYZ', 'XXXXXXXXXXXXXXXXXXXXXX' ), 'X'), 'X') = 0 )
    )


    Again, this only works with specific assumptions about your data. A trigger would be more flexible.

    HTH,

    Heath

    Edit to handle NULL values. Forgot to put that in first reply.

    [Edited by Heath on 08-28-2001 at 05:22 PM]

  4. #4
    Join Date
    May 2001
    Posts
    11

    Wink

    Hi
    The solution is simple u need not write any trigger for this current situation just add this check constraint to sequence column


    check (SEQUENCE LIKE '%A%' OR SEQUENCE LIKE '%C%' OR SEQUENCE LIKE '%G%' OR SEQUENCE LIKE '%T%');


    This will solve ur probs
    have fun
    sarath d

  5. #5
    Join Date
    Feb 2001
    Posts
    17

    Wink

    Hi,

    many thanks for your help!!!

    Cheers,
    Dan1

  6. #6
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Sarath,

    I believe this will check if an A, C, G, or T is present in the string. I don't think it will limit the sequence to only A, C, G, or T.

    This looks like a DNA sequence. Interesting.
    David Knight
    OCP DBA 8i, 9i, 10g

  7. #7
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    dknight is right. It will check if A, G, T, C exists and will not eliminate others. Moreover, table definition is messy. You should avoid 26 like conditions in a constraint. It is not wrong just messy.

    [Edited by kris109 on 08-30-2001 at 06:31 AM]

  8. #8
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340

    Difficult

    Ya dknight is right.
    But this is a difficult one.
    May be v can do something with the front end.

    Any solutions ??
    --------------------------
    The Time has come ....

  9. #9
    Join Date
    Aug 2000
    Posts
    17
    How about

    REPLACE(REPLACE(REPLACE(REPLACE(sequence,'A'),'C'),'G'),'T') is NULL

    Roger

Posting Permissions

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


Click Here to Expand Forum to Full Width