Home » RDBMS Server » Server Administration » Difference between unique-not null and primary key
Difference between unique-not null and primary key [message #371079] Thu, 24 August 2000 01:15 Go to next message
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.
Re: Difference between unique-not null and primary key [message #371084 is a reply to message #371079] Thu, 24 August 2000 09:05 Go to previous messageGo to next message
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.
Re: Difference between unique-not null and primary key [message #371090 is a reply to message #371084] Thu, 24 August 2000 17:51 Go to previous messageGo to next message
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!
Re: Difference between unique-not null and primary key [message #371093 is a reply to message #371084] Fri, 25 August 2000 03:28 Go to previous messageGo to next message
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.
Re: Difference between unique-not null and primary key [message #371094 is a reply to message #371084] Fri, 25 August 2000 03:42 Go to previous messageGo to next message
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;
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 Go to previous message
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
Previous Topic: Returning two rows
Next Topic: Oracle Stored Procedures and Powerbuilder
Goto Forum:
  


Current Time: Sat Oct 24 04:08:18 CDT 2020