Key Constraints In DBMS: Every DBA Should Know

Constraints are the rules that we can apply on the type of data in a table. Constraints allow us to limit the data that can be stored within a column of a table. You can use constraints to limit the data that is stored in a particular column, a whole table or an entire schema. Key constraints in dbms are set of rules that are defined for primary key in dbms.

Reliable databases ensure that constraints are always valid.To ensure that data integrity is maintained in the database, constraints are used.

The following types of constraints can be classified:

• Column-level constraints: Limits only column data

• Table level constraints: Limits table data at the table level

In this post we will particularly look into key constraints in dbms.But before that let’s first understand various types of constraints that are used in designing a reliable databases.

Types Of Constraints In DBMS

1. Domain Constraints In DBMS

Domain constraint puts constraints on domain or set values for an attribute. It states that the attribute value must be the atomic value of its domain.

2. Key Constraints In DBMS

There are a number of key constraints in dbms that ensure that an entity or record is uniquely or differently identified in the database. We will discuss this in detail later in article.

3. Entity Integrity Constraint In DBMS

The entity integrity constraint states that any attribute of primary keys must not contain a null value in any relation and should be unique. This is due to the fact that it is the key that is employed to determine specific rows within a relation. And If the primary key is an empty value, it is impossible to recognise those rows.

4. Referential Integrity Constraint In DBMS

This restriction is enforced when a foreign reference refers to the primary key of another relation. It stipulates that all values taken by the foreign keys must be either available in another relation to the primary key, or null.

Student Table

Department Table

In this above example Student table is linked to Department table via Department id but department id which is foreign key doesn’t have 14 in department table thus violatingReferential Integrity Constraint.

Keys In DBMS

A key is a data item that uniquely identifies a record. Key is simply a collection of columns that uniquely identify a record in a table. It is used to fetch or retrieve records / data-rows from data table according to the condition. There are many types of key constraints in dbms, such as column cannot store duplicates or null values. Keys can also be used to create relationships between different views or tables in a database.

Types of Keys In DBMS

1. Candidate key

A candidate key is a set of keys that can uniquely identify any row in a table.

2. Primary Key

This key is chosen from among the candidate keys and becomes an identifying key for a table. It can identify each row of data in the table.

3. Super Key

The superset of primary keys is called Super Key. The super key includes a number of attributes that can uniquely identify each row in the table.

4. Composite Key

A composite key is a combination of multiple attributes of a table that cannot be used as the key to identify a row individually but in combination can define tuple uniquely.  This is called a composite key. In above example Id and date can be considered composite key.

5. Unique Key

The unique key is similar to the primary key, but allows NULL values in the column. Here the Goverment Id can be considered as a unique key.

6. Foreign Key

A foreign value is an attribute value that acts as a primary key in another table. The foreign key can be used to link two tables. You should use care when entering data in the foreign key column, as incorrectly entered data could cause irreversible damage to the relationship between the tables.

7. Alternate Key

Alternate key is a candidate key, currently not selected as primary key of the table.

Key Constraints In DBMS

There are 3 types of Key Constraints in DBMS:

1. Primary Key Constraints In DBMS

Primary key Constraint states that

• There are no duplicate values allowed, i.e. The column designated as the primary key should be unique only.
• No NULL values are found in the column that has Primary key. Therefore, there is a Mandatory value in column that has Primary key.
• One primary key for each table exists, however Primary Key may contain multiple columns.
• There is no way to create a new row using the existing primary key value.

2. Foreign Key Constraints In DBMS

Foreign key Constraint states that

• The parent used as a reference must be unique or primary key.
• Child may have duplicates as well as nulls.
• Parent records can be erased in the event that there is no child.
• Master table can’t be updated in the event that a child exists.
• It is necessary to reference the primary key in the primary table.
• A foreign key column, as well as a constraint column must have the same data types.
• Records are not able to be added to a the child table if records in the master table doesn’t exist.
• The master table’s records can’t be deleted even if corresponding records in the child table exits.

3. Unique Key Constraint In DBMS

A unique constraint is the rule that states that the value of a key is valid only if they’re unique. The key which is restricted to have unique values is referred to as a unique key . 

What is the use of data constraints?

Constraints are used to:

• Avoid bad data being entered into tables.
• At the database level, it helps to enforce business logic.
• Improves database performance.
• Enforces uniqueness and avoid redundant data to the database.

Got a question or just want to chat? Comment below or drop by our forums, where a bunch of the friendliest people you’ll ever run into will be happy to help you out!