Constraints in SQL

Constraints in SQL are guidelines and limitations that can be applied to columns or tables to guarantee the accuracy and consistency of the data. Data integrity is the quality of having truthful, comprehensive, original, and non-duplicate data that complies with regulations. The term "data consistency" refers to the need that any changes made to a single database item in one table be reflected in all other tables where that object exists, subject to certain limitations.

There are different types of constraints in SQL:

1. NOT NULL constraint: This constraint ensures that a column cannot contain a NULL value. It enforces the requirement that all values in the column must be non-null.

2. UNIQUE constraint: This constraint ensures that each value in a column is unique. It prohibits duplicate values in a column.

3. PRIMARY KEY constraint: This constraint ensures that a column or set of columns uniquely identifies each row in a table. It is a combination of a NOT NULL and UNIQUE constraint.

4. FOREIGN KEY constraint: This constraint ensures that values in a column match the values in another table's primary key column. It creates a relationship between two tables.

5. CHECK constraint: This constraint ensures that values in a column satisfy a specified condition or expression. It restricts values in a column based on a logical expression.

6. DEFAULT constraint: This constraint assigns a default value to a column when a new row is inserted into the table if no value is explicitly provided.

Here is an example of creating a table with constraints:

CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Email VARCHAR(100) UNIQUE, Age INT CHECK (Age >= 18), Country VARCHAR(50) DEFAULT 'USA', OrderID INT FOREIGN KEY REFERENCES Orders(OrderID) );

In this example, a table is created named "Customers" with several constraints. The "CustomerID" column is defined as a primary key, the "FirstName" and "LastName" columns are defined as not null, the "Email" column is defined as unique, the "Age" column is defined with a check constraint to ensure that age is greater than or equal to 18, the "Country" column is defined with a default value of 'USA', and the "OrderID" column is defined as a foreign key to the "Orders" table. 

Let's explore each constraint type in more detail:

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain a NULL value. If you try to insert a NULL value into a NOT NULL column, you will receive an error message.

The syntax for adding a NOT NULL constraint is:

ALTER TABLE table_name

ALTER COLUMN column_name data_type NOT NULL;

UNIQUE Constraint

The UNIQUE constraint ensures that each value in a column is unique. It prohibits duplicate values in a column.

The syntax for adding a UNIQUE constraint is:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (column_name);                                                                                              

PRIMARY KEY Constraint

The PRIMARY KEY constraint ensures that a column or set of columns uniquely identifies each row in a table. It is a combination of NOT NULL and UNIQUE constraint.

The syntax for adding a PRIMARY KEY constraint is:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

FOREIGN KEY Constraint

The FOREIGN KEY constraint ensures that values in a column match the values in another table's primary key column. It creates a relationship between two tables.

The syntax for adding a FOREIGN KEY constraint is: ALTER TABLE table_name

ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)

REFERENCES parent_table (parent_column_name);

CHECK Constraint

The CHECK constraint ensures that values in a column satisfy a specified condition or expression. It restricts values in a column based on a logical expression.

The syntax for adding a CHECK constraint is:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name CHECK (condition);

DEFAULT Constraint

The DEFAULT constraint assigns a default value to a column when a new row is inserted into the table if no value is explicitly provided.

The syntax for adding a DEFAULT constraint is:

ALTER TABLE table_name

ALTER COLUMN column_name SET DEFAULT default_value;