Solved

unique constraint vs primary key

Posted on 2001-07-10
7
1,004 Views
Last Modified: 2008-11-14
Hi I am wondering what's the difference between declaring a column to be a unique constraint and declaring it to be a primary key. How do I specify multiple columns to be the primary key in Oracle? Thanks...
0
Comment
Question by:janwui
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Accepted Solution

by:
annu_vinayak earned 50 total points
ID: 6271710
Hi,

Primary key of a table is one of the candidate keys that gives some special features. You can have only one Primary key in the table that cannot contain NULL values, whereas in the case of Unique keys, you may have as many as unique constarints/columns but only one Primary key constriant.

Secondly, as said earlier Primary key column/columns cannot have NULL values whereas a column declared as Unique can contain NULL values.

As far as declaring multiple columns as primary keys, i am enaclosing a small DDL statement of table creation with two columns as primary key: -

Create table Employee
(EMPNO number(10),
 SO_SEC_NO varchar(15),
 Date_of_join date,
 SALARY number(5),
 DEPTNO number(2)
 constraint EMP_PK PRIMARY KEY(Empno, So_sec_no);

In the above DDL statement table we r creating Employee table with two columns(empno, so_sec_no) as primary key.

Whereas in the 'Unique' constriant case u can simply affix 'uniqe' at the end of the column definition for e.g.

Create table Deptno
(DEPTNO number(10)unique,
 Location varchar(20));

Hope that above two examples will calrify your query
0
 
LVL 1

Expert Comment

by:annu_vinayak
ID: 6271711
Hi,

Primary key of a table is one of the candidate keys that gives some special features. You can have only one Primary key in the table that cannot contain NULL values, whereas in the case of Unique keys, you may have as many as unique constarints/columns but only one Primary key constriant.

Secondly, as said earlier Primary key column/columns cannot have NULL values whereas a column declared as Unique can contain NULL values.

As far as declaring multiple columns as primary keys, i am enaclosing a small DDL statement of table creation with two columns as primary key: -

Create table Employee
(EMPNO number(10),
 SO_SEC_NO varchar(15),
 Date_of_join date,
 SALARY number(5),
 DEPTNO number(2)
 constraint EMP_PK PRIMARY KEY(Empno, So_sec_no);

In the above DDL statement table we r creating Employee table with two columns(empno, so_sec_no) as primary key.

Whereas in the 'Unique' constriant case u can simply affix 'uniqe' at the end of the column definition for e.g.

Create table Deptno
(DEPTNO number(10)unique,
 Location varchar(20));

Hope that above two examples will clarify your query
0
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6271914
Evrything said above is true. Primary Key Columns by default get two constraints 'Unique' and 'Not Null'
where as 'Unique' column is just unique.

Meaning Primary Key Column values should be unique and can't be null, while unique column values can be null but should be unique.
Syntax is just as above.

Regards
Usama!
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6272031
A primary key is technically on , as said above, unique and not null column(s)...
The main idea of a primary key is to be able to use this field as a foreign key for other tables. An order table will have a CustomerID field, which will be a foreign key to the primary key of the Customer table.
You can have only 1 primary key per table, but you can have several Unique constraints per table.
CHeers
0
 
LVL 1

Expert Comment

by:alx512
ID: 6272834
This is not a good idea to create a primary key with multiplle colums. I use only numeric single field primary key, if possible. If you need a unique value in multiplay fields, will be better to create a unique constraint in additional to primary key.
0
 
LVL 2

Expert Comment

by:jammalk
ID: 6273738
I have few things to add:
1. You can have a foreign key that references a primary key and ALSO UNIQUE KEY.
2. You can create a unique key at the table level also, not only just at the column level.

SQL> create table t1(no number(10), name varchar2(10),
    constraint t1_uk unique (no));
Table created.

This is just some clarifications on few others' comments
Jammalk
0
 

Author Comment

by:janwui
ID: 6273833
Thanks...it helps a lot.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question