CODEWITHSUNDEEP
sqloracle-database
tylerauerbeck
tylerauerbeck

Reputation: 173

Creating a constraint on multiple columns based upon the value of another column

I'm trying to create a check on a few columns in my database that forces them to be null unless the value of another column is yes. I've tried something like

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

But that doesn't seem to be working. Is there a way to do it like this, or is there a better way around this problem? Any help in the right direction would be great.

Upvotes: 4

Views: 2647

Answers (2)

Wyzard
Wyzard

Reputation: 34563

If you want to require that col1 is null unless col2 is 'y', you can write your CHECK constraint as:

col2 = 'y' OR col1 IS NULL

If you additionally want to require that col1 must have a value when col2 is 'y', you can write the constraint as:

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

You should write this as a table constraint, btw. I don't think a column constraint is allowed to refer to other columns. (But a column constraint is just another way of writing a table constraint anyway, so you don't lose anything by writing table constraints instead.)

Upvotes: 5

pratik garg
pratik garg

Reputation: 3342

so many option is there ...

but check usually used to check the row.. and if condition doesn't satisfying then this row will ignore by database...

you can try 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;

it may be having some syntax error .. please check and correct it .. I don't have database in my machine as of now ..

Upvotes: 0

Related Questions