Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

unique constraint vs primary key

Posted on 2001-07-10
7
Medium Priority
?
1,008 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

610 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