SQL with Manoj

Primary Key & Unique Key are nothing but Constraints that can be set at column level of a table, to maintain Uniqueness in the table and thus not allowing duplicate entries.
 

–> Primary Key (PK) Constraint:

1. A PRIMARY KEY uniquely identifies every row in a database table.

2. The PK constraint on a Table’s COLUMN enforces:
– – UNIQUE values and
– – NOT NULL values

3. Every table can have only one PK defined on a particular column (or more than 1 columns).

4. You can create PK on a Table with:
– – CREATE TABLE statement (in two ways)
– – or later with ALTER TABLE … ADD CONSTRAINT statement.

5. You can create a PK with an IDENTITY column.

6. PK can be Composite Key, containing more than one column.

PRIMARY Key and INDEXes:

7. In SQL Server on creating a Primary Key on a table, a Clustered Index is created with PK column as the Clustering Key.

8. You can also create a Primary Key with a Non-Clustered Index, check this blog post.
 

Check the video on Primary Keys:

–> Unique Key (PK) Constraint:

1. A UNIQUE KEY just like PK uniquely identifies every row in a database table.

2. The UK constraint on a Table’s COLUMN enforces:
– – UNIQUE values (no duplicate values)
– – Allow single NULL value (But PK do not allow NULL values)

3. A table can have one or more than one UK defined on many columns (or more than 1 columns). But only 1 PK.

4. You can create UK on a Table with:
– – CREATE TABLE statement (in two ways)
– – or later with ALTER TABLE … ADD CONSTRAINT statement.

6. UK can be Composite Key, containing more than one column.

7. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

Unique Key and INDEXes:

8. In SQL Server on creating a Unique Key on a table, a Non-Clustered Index is created with the column(s) as the Index Key.
 

Check the video on Unique Keys:

To know about more on Constraints and their types check this blog post.