Introduction to Constraints in DBMS
PRIMARY KEY
In Oracle, a primary key is a single field or combination of fields that uniquely defines a record.
None of the fields that are part of the primary key can contain a null value. A table can have only
one primary key.
Note
In Oracle, a primary key can not contain more than 32 columns.
A primary key can be defined in either a CREATE TABLE statement or an ALTER
TABLE statement.
Create Primary Key - Using CREATE TABLE statement
Syntax
The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);
Example-1
In this example, we've created a primary key on the supplier table called supplier_pk. It consists
of only one field - the supplier_id field.
CREATE TABLE supplier
(
supplier_id numeric(10),
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
Create a primary key with more than one field as in the example below:
CREATE TABLE supplier
(
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);
Create Primary Key - Using ALTER TABLE statement
Syntax
The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...
column_n);
Example
In this example, we've created a primary key on the existing supplier table called
supplier_pk. It consists of the field called supplier_id.
ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);
We could also create a primary key with more than one field as in the example below:
ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);
Drop Primary Key
Drop a primary key in Oracle using the ALTER TABLE statement.
Syntax
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example
In this example, we're dropping a primary key on the supplier table called supplier_pk.
ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;
Disable Primary Key
Disable a primary key in Oracle using the ALTER TABLE statement.
Syntax
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
Example
In this example, we're disabling a primary key on the supplier table called supplier_pk.
ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;
Enable Primary Key
Enable a primary key in Oracle using the ALTER TABLE statement.
Syntax
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
Example
In this example, we're enabling a primary key on the supplier table called supplier_pk.
ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;
Foreign key
A foreign key is a way to enforce referential integrity within your Oracle database. A foreign
key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called
the child table. The foreign key in the child table will generally reference a primary key in the
parent table.
A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE
statement.
Using a CREATE TABLE statement
Syntax
The syntax for creating a foreign key using a CREATE TABLE statement is:
CREATE TABLE table_name
(
column1 data type, column2 data type,…..
CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
);
Example: In this example, we've created a primary key on the supplier table
called supplier_pk. It consists of only one field - the supplier_id field.
Then we've created a foreign key called fk_supplier on the products table that references
the supplier table based on the supplier_id field.
CREATE TABLE supplier
( supplier_id numeric(10),
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id));
CREATE Foreign key on more than one field:
We could also create a foreign key with more than one field as in the example below:
EXAMPLE:
In this example, our foreign key called fk_foreign_comp references the supplier table based
on two fields - the supplier_id and supplier_name fields.
CREATE TABLE supplier
( supplier_id numeric(10),
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
);
Using an ALTER TABLE statement
Syntax
The syntax for creating a foreign key in an ALTER TABLE statement is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);
Example: In this example, we've created a foreign key called fk_supplier that references the
supplier table based on the supplier_id field.
ALTER TABLE products
ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id);
We could also create a foreign key with more than one field as in the example below:
ALTER TABLE products
ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name);
Foreign key with Cascade DELETE
A foreign key with cascade delete means that if a record in the parent table is deleted, then the
corresponding records in the child table will automatically be deleted. This is called a cascade
delete in Oracle.
A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an
ALTER TABLE statement.
Using a CREATE TABLE statement
Syntax
The syntax for creating a foreign key with cascade delete using a CREATE TABLE statement in
Oracle/PLSQL is:
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id));
ON DELETE CASCADE
);
Example
Let's look at an example of how to create a foreign key with cascade delete using the CREATE
TABLE statement in Oracle/PLSQL.
For example:
CREATE TABLE supplier
( supplier_id numeric(10),
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
CREATE TABLE products
( product_id numeric(10),
supplier_id numeric(10),
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE
);
In this example, because of the cascade delete, when a record in the supplier table is deleted,
all records in the products table will also be deleted that have the same supplier_id value.
Create a foreign key (with a cascade delete) with more than one field as in the example
below:
CREATE TABLE supplier
( supplier_id numeric(10),
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);
CREATE TABLE products
( product_id numeric(10),
supplier_id numeric(10),
supplier_name varchar2(50),
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE
);
The cascade delete on the foreign key called fk_foreign_comp causes all corresponding records
in the products table to be cascade deleted when a record in the supplier table is deleted, based
on supplier_id and supplier_name.
Using an ALTER TABLE statement
Syntax
The syntax for creating a foreign key with cascade delete in an ALTER TABLE statement in
Oracle/PLSQL is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE;
Example: In this example, we've created a foreign key (with a cascade delete)
called fk_supplier that references the supplier table based on the supplier_id field.
ALTER TABLE products
ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;
Create a foreign key (with a cascade delete) with more than one field as in the example
below:
ALTER TABLE products
ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE;
Foreign key with "Set NULL on Delete"
A foreign key with "set null on delete" means that if a record in the parent table is deleted, then
the corresponding records in the child table will have the foreign key fields set to null. The
records in the child table will not be deleted.
A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or
an ALTER TABLE statement.
Using a CREATE TABLE statement
Example: In this example, we've created a primary key on the supplier table called supplier_pk.
It consists of only one field - the supplier_id field. Then we've created a foreign key
called fk_supplier on the products table that references the supplier table based on the
supplier_id field.
CREATE TABLE supplier
( supplier_id numeric(10),
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10),
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE SET NULL
);
Because of the set null on delete, when a record in the supplier table is deleted, all
corresponding records in the products table will have the supplier_id values set to null.
We could also create a foreign key "set null on delete" with more than one field as in the
example below:
Example: In this example, our foreign key called fk_foreign_comp references the supplier table
based on two fields - the supplier_id and supplier_name fields.
CREATE TABLE supplier
( supplier_id numeric(10),
supplier_name varchar2(50),
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10),
supplier_name varchar2(50),
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE SET NULL
);
The delete on the foreign key called fk_foreign_comp causes all corresponding records in the
products table to have the supplier_id and supplier_name fields set to null when a record in the
supplier table is deleted, based on supplier_id and supplier_name.
Using an ALTER TABLE statement
Syntax
The syntax for creating a foreign key in an ALTER TABLE statement is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE SET NULL;
Drop the foreign key from the table.
You can do this with the ALTER TABLE statement in Oracle.
Syntax
The syntax to drop a foreign key in Oracle/PLSQL is:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example
If we then want to drop the foreign key called fk_supplier, we could execute the following
command:
ALTER TABLE products
DROP CONSTRAINT fk_supplier;
Disable a foreign key
Disable a foreign key in Oracle with syntax and examples.
Once you have created a foreign key in Oracle, you may encounter a situation where you are
required to disable the foreign key. You can do this using the ALTER TABLE statement in
Oracle.
Syntax
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
Example
In this example, we've created a primary key on the supplier table called supplier_pk. It consists
of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on
the products table that references the supplier table based on the supplier_id field.
If we then wanted to disable the foreign key called fk_supplier, we could execute the following
command:
ALTER TABLE products
DISABLE CONSTRAINT fk_supplier;
Enable a foreign key
You may encounter a foreign key in Oracle that has been disabled. You can enable the foreign
key using the ALTER TABLE statement.
Syntax
The syntax for enabling a foreign key in Oracle/PLSQL is:
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
Example
In this example, we've created a primary key on the supplier table called supplier_pk. It consists
of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on
the products table that references the supplier table based on the supplier_id field.
If the foreign key had been disabled and we wanted to enable it, we could execute the following
command:
ALTER TABLE products
ENABLE CONSTRAINT fk_supplier;
SQL CREATE TABLE with constraint to exclude NULL value
NOT NULL CONSTRAINT:
This constraint confirms that a column can not have NULL value or column can not be left
empty in CREATE TABLE statement.
Note: The NOT NULL constraint can only be applied at column Level.
Syntax: Columnname datatype(Size) Constraint Constraintname NOT NULL
Example:
The following example creates a table. Here is the field name and data types :
Add NOT NULL constraint on first two columns using CREATE TABLE
the following SQL statement can be used :
CREATE TABLE mytest(
agent_code char(6) Constraint ac_nn NOT NULL,
agent_name char(25) NOT NULL,
working_area char(25)) ;
Using Alter command to add NOT NULL
Use Modify with Alter
Alter table mytest
Modify (working_area Constraint wa_nn NOT NULL);
To view the table structure
desc mytest
Data Lengt Preci si o Scal Primar y Null abl Defaul Commen
Table Col umn Type h n e Key e t t
MYTES
AGENT_CODE Char 6 - - - - - -
T
AGENT_NAME Char 25 - - - - - -
WORKING_ARE
Char 25 - - - - - -
A
Using Alter command to drop NOT NULL
Alter table mytest drop constraint wa_nn;
desc mytest
Data Lengt Preci si o Scal Primar y Null abl Defaul Commen
Table Col umn Type h n e Key e t t
MYTES
AGENT_CODE Char 6 - - - - - -
T
AGENT_NAME Char 25 - - - - - -
WORKING_ARE
Char 25 - - - - -
A
Using Alter command to disable NOT NULL
Alter table mytest disable constraint ac_nn;
Using Alter command to enable NOT NULL
Alter table mytest enable constraint ac_nn;
UNIQUE Constraint
A unique constraint is a single field or combination of fields that uniquely defines a record.
Note
In Oracle, a unique constraint can not contain more than 32 columns.
A unique constraint can be defined in either a CREATE TABLE statement or an ALTER
TABLE statement.
Unique constraint does not allow duplicate values in the given column.
The unique column constraint allows multiple NULL entries into the given column.
Also one table can have multiple Unique key.
Create unique Constraint - Using a CREATE TABLE statement
The syntax for creating a unique constraint using a CREATE TABLE statement in Oracle
is:
CREATE TABLE table_name
(
column1 datatype(Size) CONSTRAINT constraint_name UNIQUE,
column2 datatype(Size), column3 datatype(Size),
...
CONSTRAINT constraint_name UNIQUE (column2, column 3)
);
Example
In this example, we've created a unique constraint on the supplier table called
supplier_unique. It consists of only one field - the supplier_id field.
CREATE TABLE supplier
( supplier_id numeric(10) NOT NULL,
supplier_name varchar2(50) NOT NULL,
contact_name varchar2(50),
CONSTRAINT supplier_unique UNIQUE (supplier_id)
);
Example:
We could also create a unique constraint with more than one field as in the example
below:
CREATE TABLE supplier
( supplier_id numeric(10) NOT NULL,
supplier_name varchar2(50) NOT NULL,
contact_name varchar2(50),
CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name)
);
Create unique contraint - Using an ALTER TABLE statement
The syntax for creating a unique constraint using an ALTER TABLE statement in Oracle
is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
Example
In this example, we've created a unique constraint on the existing supplier table called
supplier_unique. It consists of the field called supplier_id.
ALTER TABLE supplier
ADD CONSTRAINT supplier_unique UNIQUE (supplier_id);
Create a unique constraint with more than one field as in the example below:
ALTER TABLE supplier
ADD CONSTRAINT supplier_name_unique UNIQUE (supplier_id, supplier_name);
Drop Unique Constraint
The syntax for dropping a unique constraint in Oracle is:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example
In this example, we're dropping a unique constraint on the supplier table called
supplier_unique.
ALTER TABLE supplier
DROP CONSTRAINT supplier_unique;
Disable Unique Constraint
The syntax for disabling a unique constraint in Oracle is:
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
Enable Unique Constraint
The syntax for enabling a unique constraint in Oracle is:
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
Example
In this example, we're disabling a unique constraint on the supplier table called
supplier_unique.
ALTER TABLE supplier
DISABLE CONSTRAINT supplier_unique;
Example
In this example, we're enabling a unique constraint on the supplier table called
supplier_unique.
ALTER TABLE supplier
ENABLE CONSTRAINT supplier_unique;
.
SQL CREATE TABLE with CHECK CONSTRAINT
The SQL CHECK CONSTRAINT ensures that a value for a specific column or columns for
each row or record has satisfied a specified condition.
To satisfy the constraint, each row in the table must make the condition either TRUE or
unknown (due to a null). When Oracle evaluates a check constraint condition for a particular
row, any column names in the condition refer to the column values in that row.
Restrictions on Check Constraints Check constraints are subject to the following
restrictions:
You cannot specify a check constraint for a view.
However, you can define the view using the WITH CHECK OPTION clause, which is
equivalent to specifying a check constraint for the view.
The condition of a check constraint can refer to any column in the table, but it cannot
refer to columns of other tables.
Subqueries and scalar subquery expressions
Nested table columns or attributes
The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM.
Date constants that are not fully specified
Using a CREATE TABLE statement
The syntax for creating a check constraint using a CREATE TABLE statement in Oracle is:
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);
The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword,
the constraint will be created, but the condition will not be enforced.
Example: In this first example, we've created a check constraint on the suppliers table called
check_supplier_id. This constraint ensures that the supplier_id field contains values between
100 and 9999.
CREATE TABLE suppliers
(
supplier_id numeric(4),
supplier_name varchar2(50),
CONSTRAINT check_supplier_id
CHECK (supplier_id BETWEEN 100 and 9999));
);
Ex-2
In this second example, we've created a check constraint called check_supplier_name. This
constraint ensures that the supplier_name column always contains uppercase characters.
CREATE TABLE suppliers
(
supplier_id numeric(4),
supplier_name varchar2(50),
CONSTRAINT check_supplier_name
CHECK (supplier_name = upper(supplier_name))
);
Using an ALTER TABLE statement
The syntax for creating a check constraint in an ALTER TABLE statement in Oracle is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];
The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword,
the constraint will be created, but the condition will not be enforced.
Ex-1 In this example, we've created a check constraint on the existing suppliers table called
check_supplier_name. It ensures that the supplier_name field only contains the following
values: IBM, Microsoft, or NVIDIA and also make supplier_id Primary Key..
ALTER TABLE suppliers
ADD CONSTRAINT check_supplier_name1
CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'))
ADD CONSTRAINT pk PRIMARY KEY(supplier_id);
Drop a Check Constraint
The syntax for dropping a check constraint is:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example-1 In this example, we're dropping a check constraint on the suppliers table called
check_supplier_id.
ALTER TABLE suppliers
DROP CONSTRAINT check_supplier_id;
Enable a Check Constraint
The syntax for enabling a check constraint in Oracle is:
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
Example- In this example, we're enabling a check constraint on the suppliers table called
check_supplier_id.
ALTER TABLE suppliers
ENABLE CONSTRAINT check_supplier_id;
Disable a Check Constraint
The syntax for disabling a check constraint in Oracle is:
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
Example-In this example, we're disabling a check constraint on the suppliers table called
check_supplier_id.
ALTER TABLE suppliers
DISABLE CONSTRAINT check_supplier_id;
SQL DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column.
The default value will be added to all new records IF no other value is specified.
DEFAULT constraint specified only at column level.
SQL DEFAULT on CREATE TABLE
Ex-1The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is
created:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
SQL DEFAULT on ALTER TABLE
Ex-1) To create a DEFAULT constraint on the "City" column when the table is already created,
use the following SQL:
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
DROP a DEFAULT Constraint
Now we can remove the default value, by resetting it to NULL. The syntax is the same as
adding the default:
ALTER TABLE Persons
MODIFY City DEFAULT NULL;
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 -
Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6 No UNIQUE
agent_name char 25 No UNIQUE
working_area char 25 Yes DEFAULT
Commission decimal 8 2 Yes
The following SQL statement can be used :
CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) DEFAULT 'Mumbai',
commission decimal(8,2));
SQL CREATE TABLE using default values and CHECK CONSTRAINT
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 this 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 -
Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6 No UNIQUE
agent_name char 25 No UNIQUE
working_area char 25 Yes DEFAULT
commission decimal 8 2 Yes CHECK
The following SQL statement can be used :
CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) DEFAULT 'Mumbai',
commission decimal(8,2)
CHECK(commission>.1 AND commission<.3));
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 -
Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6 No UNIQUE
agent_name char 25 No UNIQUE
working_area char 25 No CHECK
commission integer CHECK
The following SQL statement can be used :
CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) CHECK
( working_area IN('London','Brisban','Chennai','Mumbai')) ,
commission decimal CHECK(commission<1));
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 -
Field Name Data Type Size Decimal Places NULL Constraint
ord_num Decimal 6 No UNIQUE
ord_amount Decimal 12 2 Yes
ord_date Char 10 No CHECK
cust_code Char 6 No
agent_code Char 6 No
CREATE TABLE mytest(
ord_num decimal(6) NOT NULL UNIQUE ,
ord_amount decimal(12,2) ,
ord_date char(10) NOT NULL CHECK
(ord_date LIKE '--/--/----' ),
cust_code char(6) NOT NULL ,
agent_code char(6) NOT NULL
);
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 -
Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6 No UNIQUE
agent_name char 25 No UNIQUE
working_area char 25 Yes CHECK
commission decimal 8 2 Yes CHECK
SQL Code:
CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) ,
commission decimal(8,2) ,
CHECK(commission<.20 OR working_area='London'));
SQL CREATE TABLE using CHECK CONSTRAINT and AND, OR operator
Example:
To include a CHECK CONSTRAINT on 'commission' and 'working_area' column this 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-
Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6 No UNIQUE
agent_name char 25 No UNIQUE
working_area char 25 Yes CHECK
commission decimal 8 2 Yes CHECK
SQL Code:
CREATE TABLE mytest (
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) ,
commission decimal(8,2) ,
CHECK((commission<.14 AND working_area='London')
OR (commission<.15 AND working_area='Mumbai')
OR (commission<.13 AND working_area='New York')));
