Database Constraint

Database constraints are a key feature of database management systems. They ensure that rules defined at data model creation are enforced when the data is manipulated ( inserted, updated, or deleted) in a database.
Constraints allow us to rely on the database to ensure integrity, accuracy, and reliability of the data stored in it. They are different from validations or controls we define at application or presentation layers; nor do they depend on the experience or knowledge of the users interacting with the system.
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level.

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.
The PRIMARY KEY integrity constraint guarantees that both of the following are true: No two rows of a table have duplicate values in the specified column or set of columns. The primary key columns do not allow nulls. That is, a value must exist for the primary key columns in each row.
The example of real world uses of PRIMARY KEY is Driver's licenses because they can officially identify each user as a licensed driver and their street address in the Department of Motor Vehicles' database. Student ID. Students are routinely given a unique ID known as a student ID.
Example Code:

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
The FOREIGN KEY links two tables together by referencing a column in another table. This maintains data integrity across related tables. Deleting/updating a referenced record can also be prevented by a foreign key.
Foreign keys are in everything, not only databases, but in real life too. If you have a Client ID written on a card, or a sent invoice, that is a foreign key. An ID, that links to somewhere, where you can use this value to search the correct person.
Example Code :

A unique constraint is the rule that the values of a key are valid only if they are unique. A key that is constrained to have unique values is called a unique key . A unique constraint is enforced by using a unique index.
A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique—that is, no two rows of a table have duplicate values in a specified column or set of columns.
The significance of unique constraints extends to various industries such as finance and healthcare. In a banking system where account numbers must be unique, applying this constraint ensures that no two accounts share the same identifier.
Example Code:

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
Check constraints are rules that limit the values that can be entered into a column or set of columns in a table. These rules are enforced by the database system, preventing the insertion or modification of rows that violate the specified conditions.
The practical applications of Check Constraints span various industries such as manufacturing and logistics. In a production environment where product weights must be within specific ranges, applying check constraints ensures that only valid weight measurements are recorded.
Example Code:

The NOT NULL constraint is used to ensure that a given column of a table is never assigned the null value. Once a NOT NULL constraint has been defined for a particular column, any insert or update operation that attempts to place a null value in that column will fail.
A NOT NULL constraint requires a column of a table contain no null values. For example, you can define a NOT NULL constraint to require that a value be input in the last_name column for every row of the employees table.
However, some business rules might dictate that a value must always be provided (for example, every employee is required to provide emergency contact information). The NOT NULL constraint is used to ensure that a given column of a table is never assigned the null value.
Example Code :

The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.
The DEFAULT constraint in SQL helps ensure data integrity by automatically assigning a specified default value to a column when no explicit value is provided during data insertion. This helps maintain consistent and meaningful data entries, especially for columns where it's critical to have a value, even if the user doesn't supply one.
In an online shopping platform, when a user places an order, the status of the order needs to be tracked, such as "pending," "shipped," "delivered," or "canceled." When the user first places an order, the system automatically assigns the status of "pending" without requiring the user to provide this information. This ensures that the order status is always initialized to a valid state.
Example Code :

Table of contents