100 Days of SQL

Day 61 – SQL CONSTRAINT Keyword

SQL CONSTRAINT keyword is used to define rules and restrictions on the data in a table. Constraints are used to ensure data integrity and consistency in a database, by specifying conditions that must be met by the data.

There are several types of constraints that can be defined using the CONSTRAINT keyword in SQL, including:

1. Primary key constraint: A primary key constraint is used to ensure that each row in a table is uniquely identified by a specific column or combination of columns. The syntax for defining a primary key constraint is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT pk_constraint_name PRIMARY KEY (column1, column2, ...)
);

1. Foreign key constraint: A foreign key constraint is used to ensure that data in one table corresponds to data in another table. The syntax for defining a foreign key constraint is as follows:

CREATE TABLE table_name1 (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT fk_constraint_name FOREIGN KEY (column1, column2, ...) REFERENCES table_name2(column3, column4, ...)
);

1. Unique constraint: A unique constraint is used to ensure that each value in a column is unique. The syntax for defining a unique constraint is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...)
);

1. Check constraint: A check constraint is used to ensure that the data in a column meets a specific condition or set of conditions. The syntax for defining a check constraint is as follows:

CREATE TABLE table_name (
    column1 datatype CONSTRAINT check_constraint_name CHECK (condition),
    column2 datatype,
    ...
);

1. Not null constraint: A not null constraint is used to ensure that a column cannot contain null values. The syntax for defining a not null constraint is as follows:

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype,
    ...
);

In each of these examples, the CONSTRAINT keyword is used to define a specific type of constraint on the data in a table. Constraints are a powerful tool for ensuring data integrity and consistency in a database, and they are commonly used in combination with other SQL statements to define complex data models and relationships.