How to give a unique constraint to a combination of columns in Oracle?

sql oracle constraints unique-constraint composite-key
Nigel Thomas picture Nigel Thomas · Jun 27, 2013 · Viewed 71.6k times · Source

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.

Answer

saamorim picture saamorim · Jun 27, 2013

Create a unique key on those columns

ALTER TABLE YourTable
  add CONSTRAINT YourTable_unique UNIQUE (B, C, D);

Oracle/PLSQL: Unique Constraints

Related questions

Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. The problem is that to drop a constraint for the column of a particular table I need to know …

Read More
Oracle find a constraint

I have a constraint called users.SYS_C00381400. How do I find what that constraint is? Is there a way to query all constraints?

Read More
Displaying the constraints in a table

Hello I am trying to display the constraints in one of my tables but for some reason I get the message no rows selected. Noted below is the table I have created. Create table Teams ( TeamID varCHAR2(4) constraint Teams_TeamID_…

Read More