CODEWITHSUNDEEP
sqlsql-serverprimary-key
CodeOfLife
CodeOfLife

Reputation: 313

Why we need a primary key?

I am reading about primary keys and at a lot of tutorials, technical blogs etc., and I found that a primary key cannot be null. I think it's totally wrong because I was able to insert null value in the column. I suppose a primary key can have a not null value only if the column is declared as not null. But again this is not a feature of primary keys.

My question is why do we have a concept of primary key because I find only one difference between primary key and unique key is that "Primary key can be declared only on one column whereas unique key can be declared on multiple columns". So my understanding is that why can't we also declare the primary key as a unique key if we don't have any other difference.

Upvotes: 4

Views: 11429

Answers (3)

PSK
PSK

Reputation: 17943

I suppose a primary key can have a not null value only if the column is declared as not null.But again this is not a feature of primary key.

Primary key can't have a null values. By definition of primary key, it is UNIQUE and NOT NULL.

My another question is that why do we have a concept of primary key because I find only one difference between primary key and unique key is that "Primary key can be declared only on one column whereas unique key can be declared on multiple columns"

This is completely wrong. You can create primary key on multiple columns also, the difference between Primary Key and Unique Key is Primary Key is not null and Unique key can have null values.

The main purpose of primary key is to identify the uniqueness of a row, where as unique key is to prevent the duplicates, following are the main difference between primary key and unique key.

Primary Key :

  1. There can only be one primary key for a table.
  2. The primary key consists of one or more columns.
  3. The primary key enforces the entity integrity of the table.
  4. All columns defined must be defined as NOT NULL.
  5. The primary key uniquely identifies a row.
  6. Primary keys result in CLUSTERED unique indexes by default.

Unique Key :

  1. There can be multiple unique keys defined on a table.

  2. Unique Keys result in NONCLUSTERED Unique Indexes by default.

  3. One or more columns make up a unique key.

  4. Column may be NULL, but on one NULL per column is allowed.

  5. A unique constraint can be referenced by a Foreign Key Constraint.

I suggest you read this primary key and unique key

Upvotes: 6

Sharad Pawar
Sharad Pawar

Reputation: 81

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.

A primary key’s main features are:

  • It must contain a unique value for each row of data.
  • It cannot contain null values.

A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

The primary key concept is critical to an efficient relational database. Without the primary key and closely related foreign key concepts, relational databases would not work.

A primary key, also called a primary keyword, is a key in a relational database that is unique for each record.

One Table Have Only One Primary Key.

Upvotes: 0

darpy solutions
darpy solutions

Reputation: 1

You forgot Indexing. When it comes to large data to find particular data raw it need to travel through memory record by record. To overcome that the concept of indexing is there. Primary key helps in this. So it will help to get your data access faster. After that there is concept of binary search which will helps further in that task.

Upvotes: 0

Related Questions