Solved

unique constraint vs primary key

Posted on 2001-07-10
7
998 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now