Check Constraint

[Dan1]

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

[kris109]

You need a parse function in a trigger to enforce this.

[Heath]

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]

[sarath]

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

[Dan1]

Hi,

many thanks for your help!!!

Cheers,
Dan1

[dknight]

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.

[kris109]

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]

[vijay8282]

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

Any solutions ??

[roger]

How about

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

Roger