CONSTRAINT

MySQL

Example

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT UC_Order UNIQUE (OrderNumber, PersonID)
);

Output

Query OK, 0 rows affected (0.02 sec)

Explanation

The SQL code creates a new table named ‘Orders’, with three columns: OrderID, OrderNumber, and PersonID. The Constraint ‘UC_Order’ is specified to ensure that the combination of OrderNumber and PersonID values in this table is unique. If any insertion attempts to breach this constraint (add duplicate combination), MySQL will prevent it. The PRIMARY KEY constraint ensures that OrderID is always unique.

PostgreSQL

Example

CREATE TABLE orders (
    order_id int NOT NULL,
    product_name varchar(255) NOT NULL,
    quantity int,
    CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

Output

CREATE TABLE

Explanation

The aforementioned SQL statement creates a new table named orders. This table consists of three columns: order_id (integer), product_name (varchar of 255 characters), and quantity (integer). The CONSTRAINT keyword is employed here to set a primary key on the order_id column, which is uniquely identifying each record in the table orders. The constraint named orders_pk ensures that the order_id column will not hold any duplicate or NULL values. If such values are attempted to be inserted, PostgreSQL will return an error.

SQL Server

Example

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    Age int,
    CONSTRAINT CHK_Customers_Age CHECK (Age>=18 AND Age<=150)
);

Output

(Query OK, 0 rows affected)

Explanation

In the provided SQL Server example, a CHECK constraint, CHK_Customers_Age, is created on the Customers table to ensure that the Age field contains a value between 18 and 150. If data is attempted to be inserted that does not meet this constraint, SQL Server will return an error and prevent the operation. The constraint is an integral part of maintaining data integrity in a database system.

Oracle

Example

CREATE TABLE Employee (
    ID int,
    Name varchar(255),
    Age int,
    CONSTRAINT CHK_Person CHECK (Age>=18 and Age<=60)
);

Output

Table Employee has been created.

Explanation

In the given SQL code, a table named Employee is being created with three columns: ID, Name, and Age. A CHECK constraint (CHK_Person) is defined on the Age column ensuring that the value of Age remains within the range 18 to 60. This CHECK constraint will prevent any data insertion that does not comply with the defined constraint.

SQLite

Example

CREATE TABLE Employee (
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL CHECK (AGE >= 18)
);

Output

The command does not generate an output. SQLite confirms command execution without an actual output.

Explanation

In the provided SQL code, a table called Employee is created with three columns: ID, NAME, and AGE. CONSTRAINT in SQL is used to specify rules for the data in a table. Here, the constraint in the AGE column ensures that no employee under the age of 18 can be added to the table. This is specified by the CHECK (AGE >= 18) clause.