Database Constraints

Hello Friends,

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

Which are the useful database constraints?

These database constraints 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 the database is done then more than half work is done.

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

So Now we look at 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 the same as the primary key but it has a 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 the cluster index and non- cluster index…..?

Cluster index:

In this context, all the id or primary keys 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 the 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 of 16 foreign keys in one single database table. Although you can have

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

It is database constraints 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 from 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.

Conclusion:

So in this blog post, we have learned about the database constraints. We learned about a primary key, unique key, foreign key. Primary key a unique column in our table that can identify the whole records or tuple value. A foreign key is used to establish a relationship with another table, the foreign key of a table become primary key of another table. Unique key is just like a primary key which identifies the whole record from one column value. It only differs in few manners from primary keys like cluster index and null value assignment.

For any query please feel free to write at us at a5theorys@gmail.com.

Hope! You would have liked this blog post Database Constraints!

Have a great time! Sayonara!

Anurag

I am a blogger by passion, a software engineer by profession, a singer by consideration and rest of things that I do is for my destination.