SQL Constraints

SQL Constraints

SQL constraints are rules that define how data can be inserted, updated, or deleted from a table. Constraints ensure the accuracy and reliability of the data in the database. There are different types of constraints in SQL, such as:

- NOT NULL: This constraint prevents null values from being entered into a column. For example, if a table has a column for employee name, we can use the NOT NULL constraint to make sure that every employee has a name.

- UNIQUE: This constraint ensures that each value in a column is unique. For example, if a table has a column for employee ID, we can use the UNIQUE constraint to make sure that no two employees have the same ID.

- PRIMARY KEY: This constraint identifies the main column or columns that uniquely identify each row in a table. A primary key can be composed of one or more columns. For example, if a table has columns for employee ID and department ID, we can use both columns as the primary key to identify each employee in each department.

- FOREIGN KEY: This constraint establishes a relationship between two tables by referencing a column or columns in another table. A foreign key must match the primary key of the referenced table. For example, if a table has a column for department ID, we can use it as a foreign key to reference the department ID column in another table that stores department information.

- CHECK: This constraint allows us to specify a condition that must be met by each value in a column. For example, if a table has a column for employee salary, we can use the CHECK constraint to make sure that the salary is positive and less than a certain amount.

- DEFAULT: This constraint provides a default value for a column when no value is specified. For example, if a table has a column for employee hire date, we can use the DEFAULT constraint to assign the current date as the hire date if none is given.

To create a constraint, we can use the CONSTRAINT keyword followed by the name and type of the constraint in the CREATE TABLE or ALTER TABLE statements. For example:

CREATE TABLE employees (

  emp_id INT NOT NULL,

  emp_name VARCHAR(50) NOT NULL,

  emp_dept INT NOT NULL,

  emp_salary DECIMAL(10,2) CHECK (emp_salary > 0),

  emp_hire_date DATE DEFAULT GETDATE(),

  CONSTRAINT pk_employees PRIMARY KEY (emp_id),

  CONSTRAINT fk_employees_dept FOREIGN KEY (emp_dept) REFERENCES departments (dept_id)

);