CODEWITHSUNDEEP
sqloracle-database
Prabhu
Prabhu

Reputation: 125

Constraint check with variable values

I have a table called TEST:

PAR                    CHLD                   
---------------------- ---------------------- 
1                      2                      
1                      3                      
1                      4                      
2                      5                      
3                      6

The PAR and CHLD column form a composite primary key.

My requirement is that if I'm updating a value in the CHLD column it should only accept any of the existing values in the column.

This should fail because value 7 is not in column CHLD:

UPDATE TEST SET CHLD = 7 WHERE PAR = 3;

This should succeed because value 4 is in column CHLD

UPDATE TEST SET CHLD = 4 WHERE PAR = 3;

Please note I cannot have check constraint for fixed values as I dont know the list of values during table design.

Upvotes: 0

Views: 340

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Create another table, e.g. CHILDREN, which contains the valid values (2,3,4,5,6). Then add a referential constraint from your TEST table to CHILDREN.

Upvotes: 1

Related Questions