database-constraints

Database Constraints

Hello Friends,

In this blog post, I am going to explain about the database constraints which are most excessively used while designing the database.

These are a primary key, unique key, and foreign key.

when we start to design the project whether it is small or big, first we start to design the database.

IF database is done then more than half work is done.

these three constraints stated above play very important role in designing the database.

So Now we look them one by one.

Primary Key:  

A primary key is a column in the table which uniquely identifies the tuple(row) values.

OR  one single unique column which can fetch all information of that tuple(row). See the image below.

Primary Key



Unique Key:

It seems to be same as the primary key but it has few differences which make it different from the primary key.

This also uniquely identifies the tuple values.

What is the difference between primary and unique key………?

Primary key makes cluster index by default, whereas unique key makes non-cluster index by default. See the image below.

unique_key



What is cluster index and non- cluster index…..?

Cluster index:

In this context, all the id or primary key are inserted in the ascending order regardless of the order of the registration.

For example employees of a company were asked for the registration for participating in a tech contest.

The employee makes the registration in the order as 202, 201,205,210….

But when their names are registered in the database their id order becomes 201,202,205,210…, So they are arranged in an ascending order whereas they did not come in the same order.

Non-cluster index:

This the opposite to the clustered index the entries are stored in the same order as they come.

In above example, the order would be the same when they are stored in the database as 202,201,205,210……

Another difference between primary key and unique is that,

In a table, there can be only one primary key whereas in a table there can be more than one unique key.

primary key does not allow null value to be inserted.

Unique key accepts the null values.


Foreign Key:

Foreign key the column in the table which helps us to extract all the information from the other table.

You are recommended maximum 16 foreign keys in one single database table. Although you can have

253 foreign keys though you will not be permitted to have this much keys for some performances issues.

It is also sometimes called as a friend key….?

Because foreign key has a friend in another table and that friend is the prime leader or primary key of that table,

So it very easy to take out the data of another table. See the image below.

foreignkey


So this was the explanation about this database constraints, We will update you with some more constraints in our upcoming blogs, so keep reading our blog post.

Hope! You would have liked this blog post!

Have a great time! Sayonara!