SQL Constraints

SQL Constraints

SQL constraints are rules applied to table columns to enforce data integrity and consistency. This command is essential for ensuring that the data in a database adheres to specific requirements, enhancing reliability and accuracy.

Types of SQL Constraints

  • NOT NULL: Ensures that a column cannot have a NULL value.
  • UNIQUE: Ensures that all values in a column are unique.
  • PRIMARY KEY: Uniquely identifies each record in a table and combines NOT NULL and UNIQUE.
  • FOREIGN KEY: Ensures referential integrity by linking to a primary key in another table.
  • CHECK: Ensures that all values in a column satisfy a specific condition.
  • DEFAULT: Sets a default value for a column when no value is specified.
  • INDEX: Improves the speed of data retrieval operations on a table.

Syntax

-- Adding constraints during table creation
CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

-- Adding constraints to an existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);
  • CREATE TABLE: This is the SQL keyword used to create a new table with constraints.
  • ALTER TABLE: This is the SQL keyword used to modify an existing table to add constraints.
  • ADD CONSTRAINT: This is used to add a constraint to an existing table.
  • constraint_name: This specifies the name of the constraint.
  • constraint_type: This specifies the type of constraint (e.g., PRIMARY KEY, FOREIGN KEY, etc.).
  • column1, column2, ...: These specify the names of the columns in the table.
  • datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.

Example

Let's go through a complete example that includes creating a database, creating a table with various constraints, and inserting data into the table.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table with Various Constraints

In this step, we create a table named employees within the previously created database, defining various constraints on the columns.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

Here, we define the employees table with columns for id, first_name, last_name, email, hire_date, and salary. Various constraints are applied to these columns: id is the primary key with auto-increment, first_name and last_name cannot be NULL, email must be unique, hire_date defaults to the current date, and salary must be greater than zero.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, salary) VALUES ('John', 'Doe', 'john.doe@example.com', 50000.00);
INSERT INTO employees (first_name, last_name, email, salary) VALUES ('Jane', 'Smith', 'jane.smith@example.com', 60000.00);
INSERT INTO employees (first_name, last_name, email, salary) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', 70000.00);
INSERT INTO employees (first_name, last_name, email, salary) VALUES ('Bob', 'Brown', 'bob.brown@example.com', -100.00);

The first three insertions will be successful because they meet all the constraints. The fourth insertion will fail because the salary value is not greater than zero, violating the CHECK constraint.

Step 4: Adding a Constraint to an Existing Table

This step involves adding a FOREIGN KEY constraint to an existing table named orders to ensure referential integrity with the employees table.

ALTER TABLE orders
ADD CONSTRAINT fk_employee_id FOREIGN KEY (employee_id) REFERENCES employees(id);

This command adds a FOREIGN KEY constraint to the employee_id column of the orders table, ensuring that each employee_id in the orders table corresponds to an existing id in the employees table.