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
inzaghiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.