SQL Constraints

How to specify constraints

Specifying constraints in SQL is a fundamental process for enforcing rules on data within a table to maintain data integrity, accuracy, and consistency. SQL constraints can be defined both during the creation of a table with the CREATE TABLE statement and after the table has been created using the ALTER TABLE statement.

Syntax

Constraints in SQL can be imposed at two different times namely - at the time of creation and after creation. Let's see the syntax of constraints one by one.

1. Constraint imposed at the time of table creation using CREATE TABLE command:

Syntax

Example:

We are imposing a UNIQUE constraint on the sample-number column of the sample table.

2. Constraint imposed after the time of table creation using ALTER TABLE command:

Syntax:

Example:

We are imposing a UNIQUE SQL constraints on the sample-number column of the sample table. We have to use MODIFY because we add a constraint after the creation of the table.

Types of Constraints in SQL

Constraints in SQL can be applied either on the table or a specific column. The constraints applied on the table are called Table level constraints on the other hand, the constraints applied on columns are called Column level constraints. Some of the most commonly used constraints are discussed below:

1. NOT NULL Constraint

• Enforces that a column cannot contain NULL values.
• Essential for ensuring data completeness in crucial fields.

Syntax

• During table creation:

• Adding to an existing column:

Examples

• Create table with NOT NULL:

  Ensures ID and Name in Person must always have a value.

• Add NOT NULL to existing column:

  Makes ID in Person mandatory for all future records.

2. UNIQUE Constraint

• Enforces uniqueness across table rows, permitting NULL values.
• Ideal for uniquely identifying records without serving as a primary key.
• Applies to data like email IDs and employee numbers, ensuring no duplicates.

Syntax

• When creating a table:

• Adding to an existing table:

Examples

• Creating a table with uniqueness:

  Creates Person table ensuring unique ID for every individual.

• Ensuring uniqueness for an existing column:

  Modifies Person to enforce unique ID values.

3. PRIMARY KEY Constraint

• Ensures uniqueness and non-nullability across all rows in a column or set of columns.
• Crucial for data identification and relational database integrity.
• Combines UNIQUE and NOT NULL constraints implicitly.

Syntax

• During table creation:

• Adding to an existing table:

Examples

• Creating a table with PRIMARY KEY:

  Sets ID as the primary key in Person table, ensuring unique, non-null identifiers.

• Adding PRIMARY KEY with ALTER TABLE:

  Designates ID as primary key in Person table, securing uniqueness and data presence.

4. FOREIGN KEY Constraint

• Establishes a relationship between two tables.
• Links a column in one table to a primary key in another.
• Prevents orphan records in the child table.

Syntax

• During table creation:

• Adding to an existing table:

Examples

• Creating a table with a FOREIGN KEY:

  Creates Order table, linking P_ID to Person table.

• Adding a FOREIGN KEY with ALTER TABLE:

  Adds foreign key to Order table, ensuring data integrity.

5. CHECK Constraint

• Ensures column data meets a specific condition.
• Used for validating data based on a rule.

Syntax

• When creating a table:

• Adding to an existing table:

Examples

• Creating a table with a CHECK constraint:

  This table ensures persons are at least 60 years old.

• Adding a CHECK constraint using ALTER TABLE:

  Modifies the Person table to enforce that Age must be over 60.

6. DEFAULT Constraint

• Automatically assigns a specified default value to a column when no other value is provided.

Syntax

• To define a DEFAULT constraint during table creation:

• To add or change a DEFAULT constraint for an existing column:

Examples

• Creating a table with a DEFAULT constraint:

  This sets 'India' as the default value for the Country column in the Person table when no specific country is provided.

• Adding a DEFAULT constraint using ALTER TABLE:

  This alters the existing Person table, setting 'India' as the default country for the Country column if no value is provided.

7. CREATE INDEX Constraint

• Accelerates data retrieval by creating indexes on table columns, supporting both unique and non-unique values.

Syntax

• To create an index on a table column:

Examples

• Creating an index on the ID column of a Person table:

  This command creates an index named P_Index on the ID column of the Person table, optimizing query performance by enabling faster data access.

Need for SQL Constraints

Data and its security and maintenance are a great concern for database administrators. They use different types of constraints to maintain database consistency. Constraints help us to achieve:

• SQL constraints help the database administrator to maintain the accuracy and reliability of the data in the table. For example, the administrator can use NOT NULL constraint on a column that is not supposed to contain a null value.
• SQL constraints help to maintain the integrity of the data during the operations performed on the table. For example, the administrator can use PRIMARY KEY constraint on a column so that the user cannot enter a value that is there already in the database.
• Constraints also help to enforce limits on the input so that the operation does not lead to abortion.

For example, the administrator can use a CHECK constraint (like a data type) on a column so that the user can only input a specified type of data. Otherwise, the database may get damaged.