?
Solved

unique constraint vs primary key

Posted on 2001-07-10
7
Medium Priority
?
1,006 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 150 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

764 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