0

I am a bit confused :

CREATE TABLE suppliers
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT suppliers_pk Unique (supplier_id,supplier_name)

);

insert into suppliers values(1,'farhan','sohail');
insert into suppliers values(1,'farhanAnsar','sohail');



CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,supplier_name)
);

insert into supplier values(1,'sname','cname');
insert into supplier values(1,'suppName','cname');

Both works fine. Then What exactly is the difference b/w them?

Can someone elaborate?

Few more things :

A table can have only one primary key. Combination of 2 or more columns can be used as a primary key, but primary keyword cannot be used on 2 separate columns? Am i correct?

user3625546
  • 1
  • 1
  • 2
  • Yes, you are correct. Also, if any of the posts helped please don't forget to upvote and/or mark one as an answer. – Islay May 20 '14 at 19:49
  • In the second part you have the contact_name not the supplier_name i the unique constraint. The suppliername is different in both examples. But the contact name is the same. So you got an dupplicate key. – Jens May 21 '14 at 09:11

3 Answers3

0

There can only be one Primary key whereas there can be multiple Unique keys. A Primary key is sort of a subset of Unique keys and has further restrictions on it e.g. cannot be null. Unique keys can still be used to identify records uniquely though.

Here's more: difference between primary key and unique key

Community
  • 1
  • 1
Islay
  • 478
  • 4
  • 17
0

The differences are mentioned here. difference between primary key and unique key

Adding to it, in SQL Server, by default Primary Key creates a clustered index while Unique constraint creates a non-clustered index on the columns. This default behavior can be changed if needed.

Community
  • 1
  • 1
Siddhardha
  • 21
  • 2
-1

The Biggest Difference between primary key constraint and unique key constraint is:

Primary key constraint not allowed to hold NULL value . Unique key constraint can hold only one NULL value.

Thats it!

Pramod S. Nikam
  • 4,271
  • 4
  • 38
  • 62