Create a column restriction based on the value of another column

Question

I am trying to create a check for multiple columns in my database, which causes them to be null if the value of the other column is not yes. I tried something like

ALTER TABLE TABLE1 ALTER Column1 CHECK (Column2 = 'y') 

But this does not seem to work. Is there a way to do this this way, or is there a better way to solve this problem? Any help in the right direction would be great.

Answer 1

If you want to require that col1 be null if col2 not equal to 'y', you can write your CHECK constraint as:

 col2 = 'y' OR col1 IS NULL 

If you also want to require col1 to have a value when col2 is 'y', you can write a constraint like:

 (col2 = 'y' AND col1 IS NOT NULL) OR (col2 != 'y' AND col1 IS NULL) 

You should write this as a table constraint, by the way. I do not think the column restriction is allowed to refer to other columns. (But a column constraint is another way to write a table constraint, so you don't lose anything by creating table constraints instead.)

Answer 2

so many options ...

but validation is usually used to validate a row .. and if the condition does not satisfy, then this row will ignore the database ...

you can try the trigger ...

 CREATE OR REPLACE TRIGGER trg_table1_col2 before INSERT ON table1 DECLARE duplicate_info EXCEPTION; BEGIN if :new.coloumn2 <>'y' then :new.column1 := null; end if; END trg_table1_col2; 

some syntax error may occur .. check and correct. At the moment, I do not have a database on my machine.