CodeJackpot - SQL Constraints

SQL Constraints

SQL Constraints are rules that are used to limit the type of data that can be inserted, updated or deleted from a table. These constraints ensure the accuracy and consistency of the data in the database. Constraints can be applied to a single column or to a group of columns in a table. There are several types of constraints in SQL, including:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value. This means that when a new record is inserted into the table, the column must have a value. If a NULL value is inserted, an error will occur. Here is an example:

CREATE TABLE employees (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INT
);

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are unique. This means that no two records can have the same value in the column. Here is an example:

CREATE TABLE employees (
  id INT NOT NULL UNIQUE,
  name VARCHAR(50) NOT NULL,
  age INT
);

PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify each record in a table. This means that each record must have a unique value in the primary key column. Here is an example:

CREATE TABLE employees (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT
);

FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to link two tables together. It ensures that the values in a column in one table match the values in a column in another table. Here is an example:

CREATE TABLE employees (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE departments (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CHECK Constraint

The CHECK constraint is used to ensure that the values in a column meet a specific condition. This means that when a new record is inserted into the table, the column must meet the condition. If the condition is not met, an error will occur. Here is an example:

CREATE TABLE employees (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT CHECK (age >= 18)
);

DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column. This means that when a new record is inserted into the table and no value is specified for the column, the default value will be used. Here is an example:

CREATE TABLE employees (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT DEFAULT 18
);

SQL Constraints are an important part of database design. They ensure the accuracy and consistency of the data in the database. By using constraints, you can prevent errors and ensure that the data is always valid.

SQL