[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2934
  • Last Modified:

creating unique constraints in oracle

I have an order table with the following columns
order_id varchar2(32)
order_name varchar2(100)
order_client_fk varchar2(100)

the order_id is the primary key.

I want to add a constraint  that the combination of the order_name and order_client_fk is unique.
What is this called i.e Is this a composite key?
How can this be created?

Thanks in advance
0
inzaghi
Asked:
inzaghi
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
fouaddbaCommented:
correct me if I am wrong,
order_client_fk is unique, but order_name is non-unique...right,
but u want the combination of order_name+order_client_fk should be unique,

if this is the case you can do this

ALTER TABLE ORDER
ADD CONSTRAINT ORDER_NAME_CLIENT_UK
UNIQUE (ORDER_NAME, ORDER_CLIENT_FK)
USING INDEX TABLESPACE ORDER_IDX -----------> this is only if u want to put index in a different tablespace, as u should do this...!
or
ALTER TABLE ORDER
ADD UNIQUE (ORDER_NAME, ORDER_CLIENT_FK)

this also works, but in this case Oracle will assign the name as SYS_C...(some number), and index will be create in the default tbsp.
but this is not corret way of doing, but this works too
0
 
marperCommented:

you can either add an unique constraint:

ALTER TABLE YOUR_TABLE ADD
CONSTRAINT UNIQUE_CONSTRAINT_NAME
 UNIQUE (order_name, order_client_fk )
 ENABLE
 VALIDATE

OR ceate an unique index

create unique index IX_NAME (order_name, order_client_fk )
0
 
inzaghiAuthor Commented:
If do not enter ENABLE  VALIDATE does this not validate the constraint?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
marperCommented:
VALIDATE should be enabled by deafult.
0
 
inzaghiAuthor Commented:
I dont understand why adding an unique index is the same as adding a unique constraint?

ALTER TABLE YOUR_TABLE ADD
CONSTRAINT UNIQUE_CONSTRAINT_NAME
 UNIQUE (order_name, order_client_fk )
 ENABLE
 VALIDATE

OR ceate an unique index

create unique index IX_NAME (order_name, order_client_fk )
0
 
johnsoneSenior Oracle DBACommented:
ENABLE VALIDATE is the default.
0
 
johnsoneSenior Oracle DBACommented:
A unique index and a unique constraint accomplish the same thing in different ways.

A unique constraint actually builds a unique index behind the scenes.

With the introduction of Oracle8 (or Oracle8i, I don't remember), you can now create true unique constraints that use an existing index to enforce the constraint.

This is what I would do in your case.  And Oracle recommends this as it is easier to maintain.  The constraint can be disabled and enabled without having to rebuild the index.

-- NOTE:  This index does not need to be built if an index already exists with these leading columns (order not important)
create index <ind> on <tab> (order_name, order_client_fk)..

alter table <tab> add constraint <cons> unique (order_name, order_client_fk);
0
 
actonwangCommented:

     >>I dont understand why adding an unique index is the same as adding a unique constraint?

          if you add an unique constraint, oracle will create an unique index to enforce the constraint if none exists. Internally, oracle uses index to enforce unique constrant.
0
 
actonwangCommented:
btw: they are not same. you can have both uniue constraint and nonunique index existing.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now