Home » SQL & PL/SQL » SQL & PL/SQL » Unique Keys per table
Unique Keys per table [message #199394] Tue, 24 October 2006 00:37 Go to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member

I have seen in the documentation of oracle 9i that we can create only one Unique Key per table but i have tried that and wat i found is i can abe to create more that one unique key in a Table .

I just want to know is that documentation problem or i have done anything wrong.

Please suggest me if i am wrong!!!!



Re: Unique Keys per table [message #199395 is a reply to message #199394] Tue, 24 October 2006 00:43 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Possibly you are mixing up primary keys (only one per table) and unique keys (as many as you like).

Regards,
Sabine
Re: Unique Keys per table [message #199397 is a reply to message #199395] Tue, 24 October 2006 01:00 Go to previous messageGo to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
Restrictions on Unique Constraints:---

A table or view can have only one unique key.
None of the columns in the unique key can have datatype LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, BFILE, or REF, or TIMESTAMP WITH TIME ZONE. However, the unique key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.

A composite unique key cannot have more than 32 columns.

You cannot designate the same column or combination of columns as both a primary key and a unique key.

You cannot specify a unique key when creating a subtable or a subview in an inheritance hierarchy. The unique key can be specified only for the top-level (root) table or view.
Re: Unique Keys per table [message #199398 is a reply to message #199397] Tue, 24 October 2006 01:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The quote above comes from here (the 9i R2 SQL Reference). It is probably a documentation bug. In the 10g R2 SQL Reference, you'll find this:
Quote:
Restrictions on Unique Constraints Unique constraints are subject to the following restrictions:
  • None of the columns in the unique key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.
  • A composite unique key cannot have more than 32 columns.
  • You cannot designate the same column or combination of columns as both a primary key and a unique key.
  • You cannot specify a unique key when creating a subview in an inheritance hierarchy. The unique key can be specified only for the top-level (root) view.

You'll notice that the confusing phrase is gone Wink In short: the documentation is wrong. Yes you can have more than one unique constraint/key on a table. I'm glad that there are people who actually read the documentation!

[edit]Checked MetaLink and found a similar discussion. The conclusion there was: documentation bug.

MHE

[Updated on: Tue, 24 October 2006 01:31]

Report message to a moderator

Re: Unique Keys per table [message #199406 is a reply to message #199398] Tue, 24 October 2006 01:31 Go to previous message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
Thanks !!! Maaher for correcting my doubt && documentation

Laughing Laughing

Previous Topic: Query Issues
Next Topic: Outer join problem
Goto Forum:
  


Current Time: Mon Dec 05 06:33:21 CST 2016

Total time taken to generate the page: 0.11787 seconds