Constraint check with variable values

Question

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.

Hi Prabhu, can you explain your question in details actually i am not getting, mainly what you want as output? — Krishna
– Krishna, Commented Jun 18, 2014 at 10:06
In your example you say that CHLD contains 1, 2, 5, and 4, and that an update from 4 to 5 should not be allowed, but an update from 4 to 2 should be allowed. What is different between updating 4 to 5 and updating 4 to 2? Both 5 and 2 are in the list of existing values. Please edit your question and add further explanation of what you're trying to accomplish. Thanks. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jun 18, 2014 at 10:53

Jeffrey Kemp · Accepted Answer · 2014-06-18 12:58:53Z

1

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.

answered Jun 18, 2014 at 12:58

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prabhu Over a year ago

Hope I have no choice other than having a table that stores list of value and having a referential constraint to that table

Collectives™ on Stack Overflow

Constraint check with variable values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related