Constraints and Keys in Database Management System are similar but they are not the same. However, keys and constraints in Database Management System are same but they are not interchangeable. Keys folder contains different keys and constraints are created in the constraints folder. Neither will create constraints on their own in the constraints folder. For example, when we use a primary key on the table, it is up to you if you want to add a constraint clause or not. If you do not include a constraint clause, SQL Server will automatically create the one for you. It acts as an internal short-cut. SQL Server does this by using a clustered index on the Primary Key.
Constraint in DBMS
Constraints enforce limits to the data and type of data that can be inserted, updated or deleted from the table. In other words, they are the restrictions on the database contents and operations. They ensure the correctness and integrity of the data in the database.
In DBMS, there are five different types of relational constraints:
- Domain Constraint
- Tuple Uniqueness Constraint
- Key Constraint
- Entity Integrity Constraint
- Referential Integrity Constraint
Domain Constraint
Domain constraint defines the domain of the value for an attribute. For example, consider the following table.
We cannot insert value ‘F’ in the above table. Only integer values can be taken by the Age attribute.
Tuple Uniqueness Constraint
It specifies that all the tuples must be unique in any relation. For example, consider the following table.
In this table, all the tuples are unique, thereby satisfying tuple uniqueness constraints.
On the other hand, the following table does not satisfy the tuple uniqueness constraint. The tuples are not unique.
Key Constraint
All the values of the Primary Key must unique and not null. For instance, the following table.
In the above table, Roll_No is a Primary Key. This relation does not satisfy key constraint as here all the values of the primary key are not unique.
Entity Integrity Constraint
It states that the primary key value cannot be null. We use the primary key to identify individual rows in the table. If the value of the primary key is null, we cannot identify those rows. For instance, consider the following table
The above table does not satisfy the entity integrity constraint. The value of the primary key is null.
Referential Integrity Constraint
It is the property of data stating that all its references are valid. It specifies that all the values in the foreign key reference the values of the primary key in the relation.
Keys in DBMS
In case, we want to retrieve any data from the database table. As a user, we have to tell from where and which data we want to retrieve from the table. The relational table is divided into rows and columns. Every column is given a name called attributes. With the help of these attributes/columns, we can retrieve the data from the table.
Do we have names for tuples? The answer is NO
Rows do not have any names. Now if rows do not have any names, then how can we retrieve a particular row(s) from the table? The user has to specify a particular value or information based on which we can retrieve a particular row from the table. Doing this, if the user is able to retrieve a particular row(s) from the table then we can say the table is accessible. The information/value based on which the row is identified is called a Key. If we do not have a key in the table, then we cannot access the table.
The analogy
The key to the table is just like a piggy bank. We put money in the piggy bank. But if we do not have any way to take out money from the piggy bank, then why do we use it to store money.
Key is an attribute or set of attributes the value of which helps you to uniquely identify a row in a table. Above all, they also allow you to find out a relationship between two tables. For instance, in the table given below, Roll No is unique and it is considered as the key to the table. With the help of the value of the key, we can identify a particular row in the table.
In other words, we can define a key as
- Roll_No->Name Age. Name and age are dependent on Roll_No.
- Closure of a key i.e. (Roll_No)+ gives you relation R.
Can we consider Name and Age as key?
DBMS has various different types of keys.
- Super key
- Primary Key
- Candidate Key
- Foreign Key
- Composite Key
- Alternate Key
Super Key
Super key is an attribute or a set of attributes that can uniquely identify a row in a table.
Primary Key
The primary key is the attribute which is unique and Not Null.
Candidate Key
A set of minimal attributes that can identify any tuple in a table.
Foreign Key
The Foreign key is the column which we use to create a relationship with another table.
Composite Key
When we use multiple attributes to identify a row in a table then it is known as a composite key.
Alternate Key
Unused candidate keys in the table are known as an alternate key.
For an introduction to Database Management System refer https://dextutor.com/introduction-to-database-management-system/