-
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
-
You need a parse function in a trigger to enforce this.
-
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]
-
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
-
Hi,
many thanks for your help!!!
Cheers,
Dan1
-
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
-
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]
-
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 ....
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|