Difference Between Unique Key And Primary Key

Primary keys and unique keys are commonly used in relational databases. In this blog, I will discuss the basics of Primary keys and Unique keys, the difference between a primary key and a unique key, and the similarities between the two.

So, let's first start from what are the similarities between these two and after that, the syntax, and by the end of this blog, you yourself will be able to differentiate between these two.

So, here we go.

Similarities between Primary Keys and Unique Keys

UNIQUE KEY

How to declare any column as a Unique key?

CREATE TABLE FirstBlog (
    ID int NOT NULL UNIQUE,
    LastName varchar(200) NOT NULL,
    FirstName varchar(200),
    Salary int
);

Desc

NOT NULL is a constraint. This means that the column ‘ID’ will not accept null values. By adding the UNIQUE constraint, we are making the ID column as the unique key, while the other column description will go as is. Like Lastname with varchar datatype will not accept NULL values but the column FirstName can accept NULL values as we have not marked any constraints and Salary will also accept NULL but integer in datatype.

Note. For the different types of constraints and datatypes in SQL servers, I will write another blog.

How to declare more than one column as a Unique key?

CREATE TABLE FirstBlog (
    ID int NOT NULL UNIQUE,
    LastName varchar(200) NOT NULL,
    FirstName varchar(200),
    Salary int,
    CONSTRAINT UC_FirstBlog UNIQUE (ID, LastName)
);

In the above representation of the declaration of the UNIQUE key on multiple columns, we can use the CONSTRAINT keyword. UC_ FirstBlog is the new name of the combination of two columns. One thing you can see in the above example is that we are able to declare two UNIQUE keys on the table FirstBlog.

How to add a UNIQUE key in an existing table.?

Let's take the above example with some corrections.

CREATE TABLE FirstBlog (
    ID int NOT NULL UNIQUE,
    LastName varchar(200) NOT NULL,
    FirstName varchar(200),
    Salary int,
    CONSTRAINT UC_FirstBlog UNIQUE (ID, LastName)
);

Execute it.

A table name with FirstBlog will be created in the database.

Now, if after that, we want to add a UNIQUE key on the ID column, then we need to ALTER the table. See below,

ALTER TABLE FirstBlog
ADD UNIQUE(ID);

In the same way, we can do this for multiple columns to make them a UNIQUE key. See below,

ALTER TABLE FirstBlog
ADD CONSTRAINT UC_FirstBlog UNIQUE(ID, LastName);