Solved

unique constraint vs primary key

Posted on 2001-07-10
7
999 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cannot open form error 6 65
oracle query help 36 82
PL/SQL Two PAYER_IDs with PAYER_TYPES these two types 4 34
Get FileTypes from a list of FileNames  in a Table field 18 43
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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

929 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

14 Experts available now in Live!

Get 1:1 Help Now