Constraints in SQL are essential rules applied to table columns to ensure data integrity, accuracy, and reliability within a database. These rules dictate the type of data that can be stored in each column, enhancing data consistency and security. The primary types of constraints include:
- NOT NULL: Ensures a column cannot store a null value, maintaining the necessity for explicit data in each row.
- UNIQUE: Demands all values in a column to be distinct, preventing duplicates and ensuring data uniqueness.
- PRIMARY KEY: Identifies each row in a table uniquely, combining the NOT NULL and UNIQUE constraints for optimal data retrieval and integrity.
- FOREIGN KEY: Establishes a relationship between columns in different tables, ensuring data consistency through referential integrity.
- CHECK: Validates that all values in a column meet a specified condition, enforcing data validity and restrictions.
- DEFAULT: Assigns a default value to a column if no other value is specified, simplifying data entry and ensuring consistency.
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.
- Constraint imposed at the time of table creation using CREATE TABLE command:
Syntax
CREATE TABLE table_name ( cloumn_name date_type(size) constraint_name, ..... )
Example:
We are imposing a UNIQUE constraint on the sample-number column of the sample table.
CREATE TABLE sample ( sample-number int UNIQUE, ..... )
- Constraint imposed after the time of table creation using ALTER TABLE command:
Syntax:
ALTER TABLE table_name ( MODIFY cloumn_name date_type(size) constraint_name, ..... )
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.
ALTER TABLE sample ( MODIFY sample-number int UNIQUE; )
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:
CREATE TABLE table_name ( column_name data_type NOT NULL ); - Adding to an existing column:
ALTER TABLE table_name MODIFY column_name data_type NOT NULL;
Examples
Create table with NOT NULL:
CREATE TABLE Person ( ID int NOT NULL, Name varchar(255) NOT NULL );Ensures ID and Name in Person must always have a value.
Add NOT NULL to existing column:
ALTER TABLE Person MODIFY ID int NOT NULL;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:
CREATE TABLE table_name ( column_name data_type UNIQUE, ... );Adding to an existing table:
ALTER TABLE table_name ADD UNIQUE (column_name);
Examples
Creating a table with uniqueness:
CREATE TABLE Person ( ID int NOT NULL UNIQUE, Name varchar(255) NOT NULL );Creates Person table ensuring unique ID for every individual.
Ensuring uniqueness for an existing column:
ALTER TABLE Person MODIFY ID int UNIQUE;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:
CREATE TABLE table_name ( column_name data_type PRIMARY KEY, ... );Adding to an existing table:
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
Examples
Creating a table with PRIMARY KEY:
CREATE TABLE Person ( ID int NOT NULL UNIQUE, Name varchar(255) NOT NULL, PRIMARY KEY (ID) );Sets ID as the primary key in Person table, ensuring unique, non-null identifiers.
Adding PRIMARY KEY with ALTER TABLE:
ALTER TABLE Person ADD PRIMARY KEY (ID);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:
CREATE TABLE child_table ( column1 data_type, ... FOREIGN KEY (column1) REFERENCES parent_table(parent_column) ); - Adding to an existing table:
ALTER TABLE child_table ADD FOREIGN KEY (column1) REFERENCES parent_table(parent_column);
Examples
Creating a table with a FOREIGN KEY:
CREATE TABLE Order ( O_ID int NOT NULL, P_ID int, PRIMARY KEY (O_ID), FOREIGN KEY (P_ID) REFERENCES Person(P_ID) );Creates Order table, linking P_ID to Person table.
Adding a FOREIGN KEY with ALTER TABLE:
ALTER TABLE Order ADD FOREIGN KEY (P_ID) REFERENCES Person(P_ID);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:
CREATE TABLE table_name ( column_name data_type, ... CHECK (condition) );Adding to an existing table:
ALTER TABLE table_name ADD CHECK (condition);
Examples
Creating a table with a CHECK constraint:
CREATE TABLE Person ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int, CHECK (Age >= 60) );This table ensures persons are at least 60 years old.
Adding a CHECK constraint using ALTER TABLE:
ALTER TABLE Person ADD CHECK (Age > 60);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:
CREATE TABLE table_name ( column_name data_type DEFAULT default_value, ... );To add or change a DEFAULT constraint for an existing column:
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;
Examples
Creating a table with a DEFAULT constraint:
CREATE TABLE Person( ID int NOT NULL, Name varchar(255) NOT NULL, Country varchar(255) DEFAULT 'India' );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:
ALTER TABLE Person ALTER Country SET DEFAULT 'India';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:
CREATE INDEX index_name ON table_name (column_name);
Examples
Creating an index on the ID column of a Person table:
CREATE INDEX P_Index ON Person (ID);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.
Conclusion
- Constraints in SQL ensure data integrity, accuracy, and reliability by imposing specific rules on database tables.
- They are essential for database administration, allowing for the enforcement of unique values, non-null requirements, and referential integrity.
- Constraints in SQL can be applied both during and after table creation, offering flexibility in database design and management.
- Key SQL constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, and CREATE INDEX, each serving a distinct purpose in data validation and optimization.
