CODEWITHSUNDEEP
sqloracle-databaseconstraintsunique-constraint
Ram
Ram

Reputation: 1803

ORACLE: How to combine UNIQUE Constraint with other column?

I'm trying to solve the below problem by adding unique constraint combining with another column.

My table:

Value    Flag
1        Y
2        Y
3        Y
1        N
2        N
3        N

Neither of them are primary key.

I can't have duplicate values for the given flag, i.e., For flag 'Y', I can't have duplicate values. Same goes for flag 'N'. 

Value    Flag
1        Y
2        Y
3        Y
3        Y        <--- This should not be allowed

I'm trying to add a constraint for this condition as below. 

  ALTER TABLE Table_name 
  ADD CONSTRAINT Value_constraint
  CHECK ((UPPER(Flag) = 'Y' AND UNIQUE(Value)) OR
         (UPPER(Flag) = 'N' AND UNIQUE(Value)));

But I'm getting the error message

SQL Error: ORA-00936: missing expression 
00936. 00000 -  "missing expression"
*Cause:    
*Action:

Could someone please help?

Upvotes: 0

Views: 911

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

If the combination is not allowed, then add a unique constraint or index:

ALTER TABLE Table_name 
  ADD CONSTRAINT Value_constraint UNIQUE (Value, Flag);

If you always want the flag to be upper case, then add a constraint to that effect as well:

ALTER TABLE Table_name 
  ADD CONSTRAINT CHK_Flag_Upper CHECK (Flag = UPPER(Flag));

Upvotes: 1

Related Questions