How to give a unique constraint to a combination of columns in Oracle?
Solution 1
Create a unique key on those columns
ALTER TABLE YourTable
add CONSTRAINT YourTable_unique UNIQUE (B, C, D);
Oracle/PLSQL: Unique Constraints
Solution 2
First of all you should drop an existing Constraint by using below ALTER Query.
ALTER TABLE table_name
DROP CONSTRAINT myUniqueConstraint;
Now, you can create a UNIQUE Constraint by using the keyword UNIQUE with the combination of required Columns.
For Example:
ALTER TABLE table_name
ADD CONSTRAINT myUniqueConstraint UNIQUE(B, C, D);
Detailed explanation of UNIQUE Constraint here.
Solution 3
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
CREATE UNIQUE INDEX constraint_name ON table_name (B,C,D)
Nigel Thomas
code(){ if(testPassed) { code(); } else code(); }
Updated on February 20, 2020Comments
-
Nigel Thomas about 4 years
I have a Table with 4 Columns
Each Column will be A,B,C,D
Column A is the Primary key. Column B has unique name constraint.
Now I want to remove the unique constraint for column B and give a unique constraint by combining the columns B, C and D. So the table will allow only one row with a particular value in columns B,C and D.
How can I give this type of a constraint?
I tried giving the composite unique key like :
ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINT_NAME UNIQUE (COLUMN_B, COLUMN_C, COLUMN_D)
But it is checking whether any one of the constraint is present rather than checking for the combination of unique key constraint.
-
Naveen Kumar Alone almost 11 years
-
Philip Rego over 6 yearsWhat's the naming convention? What if you have two unrelated unique values in a table? Then 'YourTable_unique' would be the same name.
-
saamorim over 6 years@PhilipRego, you're right on your comment. The naming convention is up to the team, company, user to decide. It is just an example.
-
wha7ever almost 5 yearsI am sorry, but I don't see difference between OP's alter query and query in this answer. Am I missing something?
-
saamorim almost 5 years@whatever, the question was edited afterwards, in where they included the answer that i gave.