SQL CONSTRAINTS

CONSTRAINTS

The SQL CONSTRAINTS are an integrity which defines some conditions that restrict the column to remain true while inserting or updating or deleting data in the column. Constraints can be specified when the table created first with CREATE TABLE statement or at the time of modification of the structure of an existing table with ALTER TABLE statement.

The SQL CONSTRAINTS are used to implement the rules of the table. If there is any violation of the constraints caused some action not performing properly on the table the action is aborted by the constraint.

Some CONSTRAINTS can be used along with the SQL CREATE TABLE statement.

The general structure of the SQL CONSTRAINT is defined as:

The CONSTRAINT keyword is followed by a constraint name followed by a column or a list of columns.

Types of SQL CONSTRAINTS

The SQL provides following types of CONSTRAINTS :

Syntax:

CREATE TABLE <table_name>(
column1    data_type[(size)]   constraint,
column2    data_type[(size)]   constraint,
...);

Parameters:

SQL CREATE TABLE with constraint to exclude NULL value

The following topic will describe how the NOT NULL CONSTRAINT confirms that a column can not have NULL value in CREATE TABLE statement.

Example:

The following example creates a table. Here is the field name and data types :

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL
    agent_code CHAR(6) NOT NULL,
    
    -- Column: agent_name with a data type CHAR(25), NOT NULL
    agent_name CHAR(25) NOT NULL,
    
    -- Column: working_area with a data type CHAR(25), NOT NULL
    working_area CHAR(25) NOT NULL
);

Explanation:

• CREATE TABLE mytest (: This statement initiates the creation of a new table named 'mytest'.
• agent_code CHAR(6) NOT NULL,: This line defines the first column of the table named 'agent_code'. It specifies that the data type of this column is CHAR, which means it can store character data. The (6) indicates that the column can hold up to 6 characters. NOT NULL indicates that this column cannot contain null values, meaning it must always have a value.
• agent_name CHAR(25) NOT NULL,: This line defines the second column of the table named 'agent_name'. Similar to 'agent_code', it's also of data type CHAR but with a length of 25 characters. Again, NOT NULL ensures that this column cannot contain null values.
• working_area CHAR(25) NOT NULL: This line defines the third column of the table named 'working_area'. Like the previous columns, it's a CHAR data type with a length of 25 characters, and it cannot contain null values.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE to check unique value

In the following topic, we are going to discuss, how the SQL UNIQUE CONSTRAINT ensures that each row for a column has different values in CREATE TABLE statement.

Example:

The following example creates a table. Here is the field name and data types:

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: ord_num with a decimal data type (6 digits, 0 decimal places), NOT NULL, and UNIQUE constraint
    ord_num DECIMAL(6) NOT NULL UNIQUE,
    
    -- Column: ord_amount with a decimal data type (12 digits, 2 decimal places)
    ord_amount DECIMAL(12, 2),
    
    -- Column: ord_date with a date data type, NOT NULL
    ord_date DATE NOT NULL,
    
    -- Column: cust_code with a data type CHAR(6), NOT NULL
    cust_code CHAR(6) NOT NULL,
    
    -- Column: agent_code with a data type CHAR(6), NOT NULL
    agent_code CHAR(6) NOT NULL
);

Explanation:

• CREATE TABLE mytest (: This statement initiates the creation of a new table named 'mytest'.
• ord_num DECIMAL(6) NOT NULL UNIQUE,: This line defines the first column of the table named 'ord_num'. It specifies that the data type of this column is DECIMAL, which means it can store numeric values with a specified precision. (6) indicates that the column can hold up to 6 digits in total, with 0 decimal places. NOT NULL indicates that this column cannot contain null values, and UNIQUE constraint ensures that each value in this column must be unique.
• ord_amount DECIMAL(12, 2),: This line defines the second column of the table named 'ord_amount'. It's also of data type DECIMAL but with a precision of (12, 2), meaning it can hold up to 12 total digits with 2 decimal places.
• ord_date DATE NOT NULL,: This line defines the third column of the table named 'ord_date'. It's of data type DATE, which means it can store date values. NOT NULL indicates that this column cannot contain null values.
• cust_code CHAR(6) NOT NULL,: This line defines the fourth column of the table named 'cust_code'. It's a CHAR data type with a length of 6 characters, and it cannot contain null values.
• agent_code CHAR(6) NOT NULL: This line defines the fifth column of the table named 'agent_code'. Similar to 'cust_code', it's also a CHAR data type with a length of 6 characters, and it cannot contain null values.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest; 

Output:

SQL CREATE TABLE to check unique value on more columns

The following example creates a table. Here is the field name and data types :

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint
    agent_code CHAR(6) NOT NULL UNIQUE,
    
    -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
    agent_name CHAR(25) NOT NULL UNIQUE,
    
    -- Column: working_area with a data type CHAR(25), NOT NULL
    working_area CHAR(25) NOT NULL,
    
    -- Column: commission with a decimal data type (5 digits, 2 decimal places)
    commission DECIMAL(5, 2)
); 

Explanation:

• CREATE TABLE mytest (: This statement initiates the creation of a new table named 'mytest'.
• agent_code CHAR(6) NOT NULL UNIQUE,: This line defines the first column of the table named 'agent_code'. It specifies that the data type of this column is CHAR, which means it can store character data. The (6) indicates that the column can hold up to 6 characters. NOT NULL indicates that this column cannot contain null values, meaning it must always have a value. The UNIQUE constraint ensures that each value in this column must be unique.
• agent_name CHAR(25) NOT NULL UNIQUE,: This line defines the second column of the table named 'agent_name'. Similar to 'agent_code', it's also of data type CHAR but with a length of 25 characters. NOT NULL ensures that this column cannot contain null values. The UNIQUE constraint ensures that each value in this column must be unique.
• working_area CHAR(25) NOT NULL,: This line defines the third column of the table named 'working_area'. Like the previous columns, it's a CHAR data type with a length of 25 characters, and it cannot contain null values.
• commission DECIMAL(5, 2): This line defines the fourth column of the table named 'commission'. It's of data type DECIMAL, meaning it can store numeric values with a specified precision. (5, 2) indicates that the column can hold up to 5 digits in total, with 2 decimal places. This column can contain null values, as there's no NOT NULL constraint specified.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE with UNIQUE CONSTRAINT

The SQL UNIQUE constraint is used to ensure that the each row for a column have a different value. In this page we are going to discuss, how the SQL UNIQUE CONSTRAINT works if it is used at the end of the CREATE TABLE statement instead of using the UNIQUE CONSTRAINT in the specific columns.

Example :

The following example creates a table. Here is the field name and data types :

the following SQL statement can be used:

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: cust_code with a data type CHAR(6), NOT NULL
    cust_code CHAR(6) NOT NULL,
    
    -- Column: cust_name with a data type CHAR(25), NOT NULL
    cust_name CHAR(25) NOT NULL,
    
    -- Column: cust_city with a data type CHAR(25), NOT NULL
    cust_city CHAR(25) NOT NULL,
    
    -- Column: grade with a data type INTEGER
    grade INTEGER,
    
    -- Column: agent_code with a data type CHAR(6), NOT NULL
    agent_code CHAR(6) NOT NULL,
    
    -- Creating a unique constraint on the combination of cust_code and agent_code columns
    UNIQUE(cust_code, agent_code)
);

Explanation:

• CREATE TABLE mytest: This line initiates the creation of a new table named 'mytest'.
• Column Definitions:
• cust_code CHAR(6) NOT NULL: Defines a column named 'cust_code' with a data type CHAR(6), indicating it can hold up to 6 characters. The NOT NULL constraint ensures that every row in this column must have a value.
• cust_name CHAR(25) NOT NULL: Defines a column named 'cust_name' with a data type CHAR(25), capable of holding up to 25 characters. The NOT NULL constraint ensures that this column must always contain a value.
• cust_city CHAR(25) NOT NULL: Similar to 'cust_name', this defines a column named 'cust_city' with a data type CHAR(25) and enforces the NOT NULL constraint.
• grade INTEGER: Defines a column named 'grade' with a data type INTEGER, capable of holding integer values.
• agent_code CHAR(6) NOT NULL: Defines a column named 'agent_code' with a data type CHAR(6), capable of holding up to 6 characters. The NOT NULL constraint ensures this column must always contain a value.
• UNIQUE Constraint:
• UNIQUE(cust_code, agent_code): This line creates a unique constraint on the combination of 'cust_code' and 'agent_code' columns. It means that the combination of values in these two columns must be unique across all rows in the table.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE with CHECK CONSTRAINT

The SQL CHECK CONSTRAINT ensures that a value for a specific column or columns has satisfied a specified condition.

The job of CHECK constraint is, to limit the values for a column of a table.

Note :

• The SQL CHECK CONSTRAINT can not be used on a VIEW.
• The SQL CHECK CONSTRAINT can not be used in a subquery.
• The SQL CHECK CONSTRAINT can also be used in ALTER TABLE and DROP TABLE statement.

The following example creates a table. The table contains a CHECK CONSTRAINT on commission column.The constraint ensures that the 'commission' must be less than 1. Here is the field name and data types :

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint
    agent_code CHAR(6) NOT NULL UNIQUE,
    
    -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
    agent_name CHAR(25) NOT NULL UNIQUE,
    
    -- Column: working_area with a data type CHAR(25), NOT NULL
    working_area CHAR(25) NOT NULL,
    
    -- Column: commission with a decimal data type, with a CHECK constraint ensuring commission is less than 1
    commission DECIMAL CHECK (commission < 1)
);

Explanation:

• CREATE TABLE mytest (: This begins the SQL statement to create a new table named 'mytest'. The 'CREATE TABLE' statement is used to define a new table in the database.
• agent_code CHAR(6) NOT NULL UNIQUE,: This line defines the first column of the table named 'agent_code'. It has a data type of CHAR with a maximum length of 6 characters. The NOT NULL constraint specifies that this column cannot contain NULL values, meaning it must have a value for every row. The UNIQUE constraint ensures that each value in this column must be unique across all rows in the table.
• agent_name CHAR(25) NOT NULL UNIQUE,: This line defines the second column of the table named 'agent_name'. It has a data type of CHAR with a maximum length of 25 characters. Similar to 'agent_code', it cannot contain NULL values (NOT NULL constraint) and must have unique values across all rows (UNIQUE constraint).
• working_area CHAR(25) NOT NULL,: This line defines the third column of the table named 'working_area'. It has a data type of CHAR with a maximum length of 25 characters. Like the previous columns, it cannot contain NULL values (NOT NULL constraint).
• commission DECIMAL CHECK (commission < 1): This line defines the fourth column of the table named 'commission'. It has a data type of DECIMAL, which is used for numeric values with decimal points. The CHECK constraint ensures that the value entered into this column must be less than 1. This constraint ensures that the commission percentage is within a valid range.

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output :

SQL CREATE TABLE using DEFAULT CONSTRAINT

The SQL DEFAULT CONSTRAINT provides a default value when specified none for a column.

Example:

To include a DEFAULT CONSTRAINT on 'working_area' column which ensures that -

1. The 'working_area' should be 'Mumbai' when specified none for this column,

at the time of creating a table whose field names and data types are -

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint
    agent_code CHAR(6) NOT NULL UNIQUE,
    
    -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
    agent_name CHAR(25) NOT NULL UNIQUE,
    
    -- Column: working_area with a data type CHAR(25), a DEFAULT value of 'Mumbai' if not specified
    working_area CHAR(25) DEFAULT 'Mumbai',
    
    -- Column: commission with a decimal data type (8 digits, 2 decimal places)
    commission DECIMAL(8, 2)
);

Explanation:

• CREATE TABLE mytest (: This begins the SQL statement to create a new table named 'mytest'. The 'CREATE TABLE' statement is used to define a new table in the database.
• agent_code CHAR(6) NOT NULL UNIQUE,: This line defines the first column of the table named 'agent_code'. It has a data type of CHAR with a maximum length of 6 characters. The NOT NULL constraint specifies that this column cannot contain NULL values, meaning it must have a value for every row. The UNIQUE constraint ensures that each value in this column must be unique across all rows in the table.
• agent_name CHAR(25) NOT NULL UNIQUE,: This line defines the second column of the table named 'agent_name'. It has a data type of CHAR with a maximum length of 25 characters. Similar to 'agent_code', it cannot contain NULL values (NOT NULL constraint) and must have unique values across all rows (UNIQUE constraint).
• working_area CHAR(25) DEFAULT 'Mumbai',: This line defines the third column of the table named 'working_area'. It has a data type of CHAR with a maximum length of 25 characters. If a value is not explicitly specified for this column during insertion, the default value 'Mumbai' will be assigned to it.
• commission DECIMAL(8, 2): This line defines the fourth column of the table named 'commission'. It has a decimal data type with a total of 8 digits, with 2 digits allowed after the decimal point. This column can store monetary values or other decimal data with up to 6 digits before the decimal point and up to 2 digits after it.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest; 

Output :

SQL CREATE TABLE using default values and CHECK CONSTRAINT

In the following topic, we have discussed the usage of SQL DEFAULT CONSTRAINT and usage of SQL CHECK CONSTRAINT while creating a table.

Example:

To include a CHECK CONSTRAINT on 'commission' and a DEFAULT CONSTRAINT on 'working_area' column which ensures that -

1. The 'commission' must be more than .1 and less than .3,

2. The 'working_area' should be 'Mumbai' when specified none for this column,

at the time of creating a table which contains the following field names and data types -

the following SQL statement can be used :

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint
    agent_code CHAR(6) NOT NULL UNIQUE,
    
    -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
    agent_name CHAR(25) NOT NULL UNIQUE,
    
    -- Column: working_area with a data type CHAR(25), a DEFAULT value of 'Mumbai' if not specified
    working_area CHAR(25) DEFAULT 'Mumbai',
    -- Column: commission with a decimal data type (8 digits, 2 decimal places)
    commission decimal(8,2)
    -- CHECK constraint ensuring commission is greater than 0.1 and less than 0.3
    CHECK (commission > 0.1 AND commission < 0.3)
);

Explanation:

• CREATE TABLE mytest (: This begins the SQL statement to create a new table named 'mytest'. The 'CREATE TABLE' statement is used to define a new table in the database.
• agent_code CHAR(6) NOT NULL UNIQUE,: This line defines the first column of the table named 'agent_code'. It has a data type of CHAR with a maximum length of 6 characters. The NOT NULL constraint specifies that this column cannot contain NULL values, meaning it must have a value for every row. The UNIQUE constraint ensures that each value in this column must be unique across all rows in the table.
• agent_name CHAR(25) NOT NULL UNIQUE,: This line defines the second column of the table named 'agent_name'. It has a data type of CHAR with a maximum length of 25 characters. Similar to 'agent_code', it cannot contain NULL values (NOT NULL constraint) and must have unique values across all rows (UNIQUE constraint).
• working_area CHAR(25) DEFAULT 'Mumbai',: This line defines the third column of the table named 'working_area'. It has a data type of CHAR with a maximum length of 25 characters. If a value is not explicitly specified for this column during insertion, the default value 'Mumbai' will be assigned to it.
• commission DECIMAL(8, 2),: This line defines the fourth column of the table named 'commission'. It has a decimal data type with a total of 8 digits, with 2 digits allowed after the decimal point. This column can store monetary values or other decimal data with up to 6 digits before the decimal point and up to 2 digits after it.
• CHECK (commission > 0.1 AND commission < 0.3): This line adds a CHECK constraint to the 'commission' column. The CHECK constraint ensures that the value entered into this column must be greater than 0.1 and less than 0.3. This constraint ensures that the commission percentage falls within the specified range.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output :

SQL CREATE TABLE with CHECK CONSTRAINT and IN operator

In the following topic, we have discussed how to use SQL IN operator along with SQL CHECK CONSTRAINT.

The condition for CHECK CONSTRAINT can be defined using any of the basic comparison operator, such as (>, <, =,>=,<=,<>) as well as BETWEEN, IN, LIKE, and NULL operator.

Example:

To include two CHECK CONSTRAINT which are -

1. The first one is on 'working_area' column which ensures that the working_area should be either 'London' or 'Brisban' or 'Chennai' or 'Mumbai',

2. The second one is on 'commission' column which ensures that commission must be less than 1,

in the following table which field name and data types are -

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint
    agent_code CHAR(6) NOT NULL UNIQUE,
    
    -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
    agent_name CHAR(25) NOT NULL UNIQUE,
    
    -- Column: working_area with a data type CHAR(25), CHECK constraint ensuring the value is one of the specified cities
    working_area CHAR(25) CHECK (working_area IN ('London', 'Brisban', 'Chennai', 'Mumbai')),
    
    -- Column: commission with a decimal data type, CHECK constraint ensuring commission is less than 1
    commission DECIMAL CHECK (commission < 1)
);

Explanation:

• CREATE TABLE mytest (: Begins the SQL statement to create a new table named 'mytest'.
• agent_code CHAR(6) NOT NULL UNIQUE,: Defines the first column named 'agent_code'. It has a data type of CHAR with a maximum length of 6 characters. The NOT NULL constraint specifies that this column cannot contain NULL values, and the UNIQUE constraint ensures that each value in this column must be unique across all rows in the table.
• agent_name CHAR(25) NOT NULL UNIQUE,: Defines the second column named 'agent_name'. It has a data type of CHAR with a maximum length of 25 characters. Similar to 'agent_code', it cannot contain NULL values (NOT NULL constraint) and must have unique values across all rows (UNIQUE constraint).
• working_area CHAR(25) CHECK (working_area IN ('London', 'Brisban', 'Chennai', 'Mumbai')),: Defines the third column named 'working_area'. It has a data type of CHAR with a maximum length of 25 characters. The CHECK constraint ensures that the value entered into this column must be one of the specified cities: 'London', 'Brisban', 'Chennai', or 'Mumbai'.
• commission DECIMAL CHECK (commission < 1): Defines the fourth column named 'commission'. It has a decimal data type. The CHECK constraint ensures that the value entered into this column must be less than 1.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE with CHECK CONSTRAINT and LIKE operator

In the following topic, we are going to discuss, how the SQL LIKE operator can be used with CHECK CONSTRAINT.

Example:

To include a CHECK CONSTRAINT on 'ord_date' column which ensures that the format of the 'ord_date' must be like '--/--/----', for example, ('18/05/1998') at the time of creating a table with following field names and data types -

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest (
    -- Column: ord_num with a decimal data type (6 digits), NOT NULL, and UNIQUE constraint
    ord_num DECIMAL(6) NOT NULL UNIQUE,
    
    -- Column: ord_amount with a decimal data type (12 digits, 2 decimal places)
    ord_amount DECIMAL(12,2),
    
    -- Column: ord_date with a data type CHAR(10), NOT NULL, and CHECK constraint ensuring the format is '--/--/----'
    ord_date CHAR(10) NOT NULL CHECK (ord_date LIKE '--/--/----'),
    
    -- Column: cust_code with a data type CHAR(6), NOT NULL
    cust_code CHAR(6) NOT NULL,
    
    -- Column: agent_code with a data type CHAR(6), NOT NULL
    agent_code CHAR(6) NOT NULL
);  

Explanation:

• CREATE TABLE mytest (: Begins the SQL statement to create a new table named 'mytest'.
• ord_num DECIMAL(6) NOT NULL UNIQUE,: Defines the first column named 'ord_num'. It has a decimal data type with 6 total digits. The NOT NULL constraint specifies that this column cannot contain NULL values, and the UNIQUE constraint ensures that each value in this column must be unique across all rows in the table.
• ord_amount DECIMAL(12,2),: Defines the second column named 'ord_amount'. It has a decimal data type with 12 total digits, with 2 digits allowed after the decimal point.
• ord_date CHAR(10) NOT NULL CHECK (ord_date LIKE '--/--/----'),: Defines the third column named 'ord_date'. It has a data type of CHAR with a length of 10 characters. The NOT NULL constraint specifies that this column cannot contain NULL values. The CHECK constraint ensures that the value entered into this column follows the specified format '--/--/----'.
• cust_code CHAR(6) NOT NULL,: Defines the fourth column named 'cust_code'. It has a data type of CHAR with a maximum length of 6 characters. The NOT NULL constraint specifies that this column cannot contain NULL values.
• agent_code CHAR(6) NOT NULL: Defines the fifth column named 'agent_code'. It has a data type of CHAR with a maximum length of 6 characters. The NOT NULL constraint specifies that this column cannot contain NULL values.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest; 

Output :

SQL CREATE TABLE with CHECK CONSTRAINT and OR operator

In the following topic, we are discussing about the usage of OR operator along with the CHECK CONSTRAINT.

Example:

To include a CHECK CONSTRAINT on 'commission' and 'working_area' column which ensures that the 'commission' must be less than .20 and 'working_area' must be 'London' at the time of creating the following table which consists the field names and data types -

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint
    agent_code CHAR(6) NOT NULL UNIQUE,
    
    -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
    agent_name CHAR(25) NOT NULL UNIQUE,
    
    -- Column: working_area with a data type CHAR(25)
    working_area CHAR(25),
    
    -- Column: commission with a decimal data type (8 digits, 2 decimal places)
    commission DECIMAL(8,2),
    
    -- CHECK constraint ensuring commission is less than 0.20 or working_area is 'London'
    CHECK (commission < 0.20 OR working_area = 'London')
);

Explanation:

• CREATE TABLE mytest (: Begins the SQL statement to create a new table named 'mytest'.
• agent_code CHAR(6) NOT NULL UNIQUE,: Defines the first column named 'agent_code'. It has a data type of CHAR with a maximum length of 6 characters. The NOT NULL constraint specifies that this column cannot contain NULL values, and the UNIQUE constraint ensures that each value in this column must be unique across all rows in the table.
• agent_name CHAR(25) NOT NULL UNIQUE,: Defines the second column named 'agent_name'. It has a data type of CHAR with a maximum length of 25 characters. Similar to 'agent_code', it cannot contain NULL values (NOT NULL constraint) and must have unique values across all rows (UNIQUE constraint).
• working_area CHAR(25),: Defines the third column named 'working_area'. It has a data type of CHAR with a maximum length of 25 characters. It does not have any constraints applied to it.
• commission DECIMAL(8,2),: Defines the fourth column named 'commission'. It has a decimal data type with a total of 8 digits, with 2 digits allowed after the decimal point.
• CHECK (commission < 0.20 OR working_area = 'London'): This line adds a CHECK constraint to the table. The constraint ensures that either the 'commission' value is less than 0.20 or the 'working_area' value is 'London'. This constraint allows for flexibility in setting conditions for the validity of data in these columns.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output :

SQL CREATE TABLE using CHECK CONSTRAINT and AND, OR operator

In the following topic, we are going to discuss the usage of OR and AND operator along with the CHECK CONSTRAINT. The condition will start to work at the time of inserting the records in the table.

Example:

To include a CHECK CONSTRAINT on 'commission' and 'working_area' column which ensures that -

1. The 'commission' must be less than .14 and 'working_area' must be 'London',

2. or the 'commission' must be less than .15 and 'working_area' must be 'Mumbai',

3. or the 'commission' must be less than .13 and 'working_area' must be 'New York'

at the time of creating the table which fields name and data types are-

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6), NOT NULL, and UNIQUE constraint
    agent_code CHAR(6) NOT NULL UNIQUE,
    
    -- Column: agent_name with a data type CHAR(25), NOT NULL, and UNIQUE constraint
    agent_name CHAR(25) NOT NULL UNIQUE,
    
    -- Column: working_area with a data type CHAR(25)
    working_area CHAR(25),
    
    -- Column: commission with a decimal data type (8 digits, 2 decimal places)
    commission DECIMAL(8,2),
    
    -- CHECK constraint ensuring specific commission and working area combinations
    CHECK (
        (commission < 0.14 AND working_area = 'London') OR
        (commission < 0.15 AND working_area = 'Mumbai') OR
        (commission < 0.13 AND working_area = 'New York')
    )
);

Explanation:

• CREATE TABLE mytest (: Begins the SQL statement to create a new table named 'mytest'.
• agent_code CHAR(6) NOT NULL UNIQUE,: Defines the first column named 'agent_code'. It has a data type of CHAR with a maximum length of 6 characters. The NOT NULL constraint specifies that this column cannot contain NULL values, and the UNIQUE constraint ensures that each value in this column must be unique across all rows in the table.
• agent_name CHAR(25) NOT NULL UNIQUE,: Defines the second column named 'agent_name'. It has a data type of CHAR with a maximum length of 25 characters. Similar to 'agent_code', it cannot contain NULL values (NOT NULL constraint) and must have unique values across all rows (UNIQUE constraint).
• working_area CHAR(25),: Defines the third column named 'working_area'. It has a data type of CHAR with a maximum length of 25 characters. It does not have any constraints applied to it.
• commission DECIMAL(8,2),: Defines the fourth column named 'commission'. It has a decimal data type with a total of 8 digits, with 2 digits allowed after the decimal point.
• CHECK ( ... ): This line adds a CHECK constraint to the table. The constraint specifies that the combination of commission and working_area must meet one of the specified conditions:
• Commission is less than 0.14 and working_area is 'London'.
• Commission is less than 0.15 and working_area is 'Mumbai'.
• Commission is less than 0.13 and working_area is 'New York'.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output :

SQL CREATE TABLE using CASCADE

The CASCADE option with ON DELETE allows deleting rows from the child table when the corresponding rows are deleted from the parent table.

The DELETE CASCADE works across a foreign key link and removes the child records associated with the parent records.

Example:

To create a table which contains the following field name and data types -

The table contains a PRIMARY KEY on 'itemcode' and a FOREIGN KEY on 'company_id' column which references to the 'company_id' column of 'company' table.

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest (
    -- Column: tranno with a decimal data type, NOT NULL
    tranno DECIMAL NOT NULL,
    
    -- Column: company_id with a varchar data type, maximum length of 6 characters
    company_id VARCHAR(6),
    
    -- Column: itemcode with a varchar data type, maximum length of 10 characters
    itemcode VARCHAR(10),
    
    -- Column: coname with a varchar data type, maximum length of 35 characters
    coname VARCHAR(35),
    
    -- Column: itemname with a varchar data type, maximum length of 35 characters
    itemname VARCHAR(35),
    
    -- Column: iqty with an integer data type
    iqty INTEGER,
    
    -- PRIMARY KEY constraint specifying 'itemcode' as the primary key
    PRIMARY KEY(itemcode),
    
    -- FOREIGN KEY constraint specifying 'company_id' as a foreign key referencing 'company' table
    FOREIGN KEY(company_id) REFERENCES company (company_id) ON DELETE CASCADE
);

Explanation:

• CREATE TABLE mytest (: Begins the SQL statement to create a new table named 'mytest'.
• tranno DECIMAL NOT NULL,: Defines the column named 'tranno'. It has a decimal data type. The NOT NULL constraint specifies that this column cannot contain NULL values.
• company_id VARCHAR(6),: Defines the column named 'company_id'. It has a varchar data type with a maximum length of 6 characters.
• itemcode VARCHAR(10),: Defines the column named 'itemcode'. It has a varchar data type with a maximum length of 10 characters.
• coname VARCHAR(35),: Defines the column named 'coname'. It has a varchar data type with a maximum length of 35 characters.
• itemname VARCHAR(35),: Defines the column named 'itemname'. It has a varchar data type with a maximum length of 35 characters.
• iqty INTEGER,: Defines the column named 'iqty'. It has an integer data type.
• PRIMARY KEY(itemcode),: Specifies the 'itemcode' column as the primary key for the table 'mytest'.
• FOREIGN KEY(company_id) REFERENCES company (company_id) ON DELETE CASCADE: Specifies a FOREIGN KEY constraint on the 'company_id' column, referencing the 'company_id' column in the 'company' table. The ON DELETE CASCADE specifies that if a record in the 'company' table is deleted, all related records in the 'mytest' table where 'company_id' matches will also be deleted.

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Foreign Key
Next: Alter Table