4

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.

tshepang
  • 12,111
  • 21
  • 91
  • 136
CodeOfLife
  • 295
  • 2
  • 11
  • 1
    You might benefit from reading about [relational algebra](https://en.wikipedia.org/wiki/Relational_model), which gives the background information on why SQL makes certain choices. In particular, "primary key" is defined there; as is uniqueness (under the name "alternate key"). SQL relaxes some of the theoretical constraints (keys don't have to be minimal), but it's still worth one's while to know the theory behind the engineering. – Amadan Feb 05 '19 at 05:26
  • Possible duplicate of [difference between primary key and unique key](https://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key) – philipxy Jun 01 '19 at 12:12

3 Answers3

6

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

PSK
  • 17,547
  • 5
  • 32
  • 43
  • If you declare a column as a primary key that doesn't give you the facility that it will store a null value.You will have to specifically mark that column as a not null column. @psk – CodeOfLife Feb 05 '19 at 11:15
  • I tried creating primary key on more than one columns and i got straight away error and this is the error "Cannot add multiple PRIMARY KEY constraints to table 'data'. ". @PSK.Also how to declare primary key on more than one columns?? – CodeOfLife Feb 05 '19 at 11:18
  • 1
    Primary key can be only one, but to create primary key you can choose multiple columns. Such primary key is also called as composite primary key – PSK Feb 05 '19 at 11:19
  • 1
    Like primary key (column_a, column_b) . please have a look to this https://stackoverflow.com/questions/3922337/how-to-create-composite-primary-key-in-sql-server-2008 – PSK Feb 05 '19 at 11:21
  • Yes your answer gave me a way to create more than one primary key and i think my reason stating that why primary key can't have null values is correct.Can you please provide your comment on that @psk – CodeOfLife Feb 05 '19 at 12:38
  • Primary Key is used to identify each row uniquely in a table, a null can't identify any row which is against the concept of primary key. I hope this make sense to you. – PSK Feb 05 '19 at 12:44
  • @PSK . . . This is a really good answer, but several of the points are specific to SQL Server. I simply cannot figure out if the question is generic or specific to SQL Server. – Gordon Linoff Feb 05 '19 at 12:47
0

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.

darpy solutions
  • 1
  • 1
0

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.

Sharad Pawar
  • 81
  • 1
  • 2