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

Question

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 I gave the constraint as you told.. but it seems that the constraint only checks for any one of the column names... I want it to be like a new row will not be allowed to enter all the three column values are already existing in the table. — Nigel Thomas, Jun 28 '13 at 06:55
hai @nigelthomas view this link http://stackoverflow.com/questions/1109614/how-can-i-create-a-sql-unique-constraint-based-on-2-columns — Naveen Kumar Alone, Jun 28 '13 at 07:05

score 65 · Accepted Answer · edited Sep 22 '15 at 14:22

65

Create a unique key on those columns

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

Oracle/PLSQL: Unique Constraints

edited Sep 22 '15 at 14:22

Glenn

8,932
2
41
54

answered Jun 27 '13 at 12:48

saamorim

3,855
17
22

What's the naming convention? What if you have two unrelated unique values in a table? Then 'YourTable_unique' would be the same name. – Philip Rego Dec 21 '17 at 20:41
@PhilipRego, you're right on your comment. The naming convention is up to the team, company, user to decide. It is just an example. – saamorim Jan 03 '18 at 21:27
1

I am sorry, but I don't see difference between OP's alter query and query in this answer. Am I missing something? – Vanity Slug - codidact.com Jun 14 '19 at 15:17
2

@whatever, the question was edited afterwards, in where they included the answer that i gave. – saamorim Jul 12 '19 at 08:13

Naveen Kumar Alone · Answer 2 · 2017-02-20T06:50:26.053

9

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.

edited Feb 20 '17 at 06:50

answered Jun 27 '13 at 12:47

Naveen Kumar Alone

7,536
5
36
57

http://stackoverflow.com/questions/1109614/how-can-i-create-a-sql-unique-constraint-based-on-2-columns – Naveen Kumar Alone Jun 28 '13 at 07:07

score 2 · Answer 3 · answered May 14 '15 at 06:09

2

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

CREATE UNIQUE INDEX constraint_name ON table_name (B,C,D)

answered May 14 '15 at 06:09

Sunil Kumar

5,477
4
31
38

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

3 Answers3

Linked

Related