Database Constraints / Key constraints in DBMS / Integrity constraints in DBMS.

Hello Friends,

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

Database constraints are also known as Integrity constraints in DBMS, CHECK constraint in SQL, Key constraints in DBMS, Integrity constraints in SQL, so don’t be confused with their names they all are the same.

Integrity constraints in DBMS or key constraints in DBMS are the main key factor to manage, arrange databases or tables, and make the relationship amongst two or more database tables.

Within this blog, we are going to cover up Integrity constraints in SQL and Types of constraints.

You would have read about the SQL database or DBMS then you will be quite familiar with CHECK constraint in SQL like UNIQUE constraint
Referential integrity constraint.

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………?

The primary key makes the clustered index by default, whereas the unique key makes a non-cluster index by default. See the image below.

unique_key



What is the clustered 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 of 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.

The 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 a 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(Integrity constraints in DBMS). We learned about Types of constraints like a primary key, unique key, foreign key(Referential integrity constraint), 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 becomes the primary key of another table.

A unique key(UNIQUE constraint) is just like a primary key that identifies the whole record from one column value. It only differs in a few manners from primary keys like the clustered 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.