OraFAQ Forum: Server Administration » Difference between unique-not null and primary key

Home » RDBMS Server » Server Administration » Difference between unique-not null and primary key

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between unique-not null and primary key [message #371079]

Thu, 24 August 2000 01:15

Jaya Kumar NM
Messages: 11
Registered: August 2000

Junior Member

What are the pros & cons of having a primary key and (Unique and not null, combined) constraints on a column.

Report message to a moderator

Re: Difference between unique-not null and primary key [message #371084 is a reply to message #371079]

Thu, 24 August 2000 09:05

Thavamani
Messages: 11
Registered: July 2000

Junior Member

Hi,
With unique + Not Null and Primary key as far the data is concerned there is no difference.When you create a primary key for a column index is created automatically whereas it is not the case with unique + Not Null.

Report message to a moderator

Re: Difference between unique-not null and primary key [message #371090 is a reply to message #371084]

Thu, 24 August 2000 17:51

Andrew again...
Messages: 270
Registered: July 2000

Senior Member

You can't create a Foreign key constraint on a table which references a not-null Unique index. It must be a primary key. It's a good idea to get into the habbit of creating Primary Keys, Foreign key contraints etc. - it's the only way to ensure data integrity. Many people don't see the need for them - don't follow them!

Report message to a moderator

Re: Difference between unique-not null and primary key [message #371093 is a reply to message #371084]

Fri, 25 August 2000 03:28

Jaya Kumar NM
Messages: 11
Registered: August 2000

Junior Member

Hi,
Thanks for your suggestion, but an index is also automatically created when u defince a column with unique constraint. I think this is not the proper solution.

Report message to a moderator

Re: Difference between unique-not null and primary key [message #371094 is a reply to message #371084]

Fri, 25 August 2000 03:42

Jaya Kumar NM
Messages: 11
Registered: August 2000

Junior Member

Hi,
You can create a foreign key constraint on a table which references to a unique or (unique+notnull) constraint. Here is the sql
alter table emp add constraint fk_dept
foreign key(dept) references
dept(dept) on delete cascade;

Report message to a moderator

Re: Difference between unique-not null and primary key - Ora 7.3.4 [message #371100 is a reply to message #371084]

Fri, 25 August 2000 13:15

Andrew again...
Messages: 270
Registered: July 2000

Senior Member

I can't creat an FK to a "not null unique index" on a table where that column(s) is not defined as a PK. I am running ORA 7.3.4 - does anyone know if this is a Version / initilization issue?

The error message alludes to the fact that it can be done (sometimes):
ORA-02270: no matching unique or primary key for this column-list

Report message to a moderator

Previous Topic:	Returning two rows
Next Topic:	Oracle Stored Procedures and Powerbuilder

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 08:20:25 CDT 2024