PL/SQL CONSTRAINTS

Oracle PRIMARY KEY

This tutorial, you will learn how to use Oracle PRIMARY KEY constraint to
manage the primary key of a table.
Introduction to the primary key

A primary key is a column of a combination of columns in a table that

uniquely identifies a row in the table.

The following are rules that make a column a primary key:

 A primary key column cannot contain a NULL value or an empty string.

 A primary key value must be unique within the entire table.
 A primary key value should not be changed over time.
According to these rules, the following are the recommendations for the
primary keys:

 First, the primary key should be meaningless. Sometimes, you may

want use meaningful data, which considers being unique, for the
primary keys e.g., social security number (SSN), vehicle identification
number (VIN), email, and phone number. However, you don’t know
when the email or phone number changes or is reused by another
person. In such cases, it will create many data problems. In the
database world, the artificial keys are known as surrogate keys which
are as opposed to natural primary keys.
 Second, the primary keys should be compact. The primary keys
typically are numeric because Oracle typically processes numbers faster
than any other data types.
It is considered a best practice have a primary key in every table though it is
not mandatory in Oracle.
To create a primary key in a table, you use the PRIMARY KEY constraint.
Oracle PRIMARY KEY constraint examples
Typically, you create a primary key for a table when you create that table. In
addition, you can add a primary key to a table after the fact by using
the ALTER TABLE statement.
Creating a primary key that consists of one column

1
The following CREATE TABLE statement creates the purchase_orderstable:
1 CREATE TABLE purchase_orders (
2 po_nr NUMBER PRIMARY KEY,
3 vendor_id NUMBER NOT NULL,
4 po_status NUMBER(1,0) NOT NULL,
5 created_at TIMESTAMP WITH TIME ZONE NOT NULL
6 );
The purchase_orders table has four columns purchase order number
(po_nr), vendor id (vendor_id), purchase order status (po_status), and the
timestamp (created_at) of which the purchase order is created.
In this table, defined the po_nr column as the primary key by using
the PRIMARY KEY clause.
Note that the PRIMARY KEY clause implicitly makes the po_nrcolumn NOT
NULL so you don’t have to define the column like:
1 po_nr NUMBER NOT NULL PRIMARY KEY
The PRIMARY KEY constraint in this example is an inline constraint because
it is on the same line as the po_nr column.
Consider the following statement.

1 CREATE TABLE purchase_orders (

2 po_nr NUMBER,
3 vendor_id NUMBER NOT NULL,
4 po_status NUMBER(1,0) NOT NULL,
5 created_at TIMESTAMP WITH TIME ZONE NOT NULL,
6 CONSTRAINT pk_purchase_orders PRIMARY KEY(po_nr)
7 );
This example used the PRIMARY KEY constraint as the table constraint.
Notice the following clause:
1 CONSTRAINT pk_purchase_orders PRIMARY KEY(po_nr)
In addition, we explicitly assigned the PRIMARY KEY constraint a
name pk_purchase_orders.
Creating a primary key that consists of multiple columns

The following statement creates the purchase order line items table:

1 CREATE TABLE purchase_order_items (

2 po_nr NUMBER NOT NULL,
3 item_nr NUMBER NOT NULL,
4 product_id NUMBER NOT NULL,
5 quantity NUMBER NOT NULL,
6 purchase_unit NUMBER NOT NULL,
7 buy_price NUMBER (9,2) NOT NULL,

2
8 delivery_date DATE,
9 PRIMARY KEY (po_nr, item_nr)
10 );
In this example, the primary key of the purchase_order_items table consists
of two columns: po_nr and item_nr. It means that the combination of values
of these columns uniquely identifies a purchase order line item.
This example did not use the CONSTRAINT clause to explicitly assign
the PRIMARY KEY constraint a name. Therefore, Oracle implicitly assigned
the primary key constraint a system-generated name such
as SYS_C0010617.

Adding a primary key to a table

Sometimes, you may want to add a primary key constraint to an existing

table. To do it, you use the ALTER TABLE statement as follows:
1 ALTER TABLE table_name
2 ADD CONSTRAINT constraint_name
3 PRIMARY KEY (column1, column2, ...);
The following example creates the vendors table first and then adds a
primary key constraint to it:
1 CREATE TABLE vendors (
2 vendor_id NUMBER,
3 vendor_name VARCHAR2(255) NOT NULL,
4 address VARCHAR2(255) NOT NULL
5 );
6
7 ALTER TABLE vendors
8 ADD CONSTRAINT pk_vendors PRIMARY KEY (vendor_id);

Dropping an Oracle PRIMARY KEY constraint

You will rarely drop a PRIMARY KEY constraint from a table. If you have to
do so, you use the following ALTER TABLE statement.
1 ALTER TABLE table_name
2 DROP CONSTRAINT primary_key_constraint_name;
For example, you can drop the primary key constraint of the vendors table
as follows:
1 ALTER TABLE vendors
2 DROP CONSTRAINT pk_vendors;
It is possible to use the following statement to drop the primary key of a
table:

1 ALTER TABLE table_name

3
2 DROP PRIMARY KEY;
For example:

1 ALTER TABLE vendors

2 DROP PRIMARY KEY;

Enable / Disable an Oracle PRIMARY KEY constraint

To improve the performance when loading a large amount of data into a
table or updating mass data, you can temporarily disable the PRIMARY
KEY constraint.
To disable a PRIMARY KEYconstraint of a table, you use the ALTER
TABLE statement:
1 ALTER TABLE table_name
2 DISABLE CONSTRAINT primary_key_constraint_name;
or

1 ALTER TABLE table_name

2 DISABLE PRIMARY KEY;
For example, to disable the primary key constraint of
the purchase_orders table, you use the following statement:
1 ALTER TABLE purchase_orders
2 DISABLE CONSTRAINT pk_purchase_orders;
or

1 ALTER TABLE purchase_orders

2 DISABLE PRIMARY KEY;

To enable a primary key constraint, you use the following ALTER

TABLE statement:
1 ALTER TABLE table_name
2 ENABLE CONSTRAINT primary_key_constraint_name;
or

1 ALTER TABLE table_name

2 ENABLE PRIMARY KEY;
The following example enables the PRIMARY KEY constraint of
the purchase_orders table:
1 ALTER TABLE purchase_orders
2 ENABLE CONSTRAINT pk_purchase_orders;
or

4
1 ALTER TABLE purchase_orders
2 ENABLE PRIMARY KEY

Oracle Foreign Key

This tutorial, you will learn how to use the Oracle foreign key to establish the
relationship between tables.

Introduction to Oracle foreign key constraint

A foreign key is all about the relationship. Let’s start with an example to
clearly understand its concept.

Suppose, we have two tables supplier_groups and suppliers:

1 CREATE TABLE supplier_groups(
2 group_id NUMBER,
3 group_name VARCHAR2(255) NOT NULL,
4 PRIMARY KEY (group_id)
5 );
6
7 CREATE TABLE suppliers (
8 supplier_id NUMBER,
9 supplier_name VARCHAR2(255) NOT NULL,
10 group_id NUMBER NOT NULL,
11 PRIMARY KEY(supplier_id)
12 );
The supplier_groups table stores supplier groups e.g., one-time supplier,
third-party supplier, and inter-co supplier. Each supplier group may have
zero, one, or many suppliers.
The suppliers table stores the supplier information. Each supplier must
belong to a supplier group.
The relationship between supplier_groups and suppliers table is one-to-
many. In other words, one supplier group has many suppliers while each
supplier must belong to a supplier group.
The group_id in the suppliers table is used for establishing the relationship
between rows in the suppliers and supplier_groups tables.
Before inserting a row into the suppliers table, you have to look up an
existing group_id in the supplier_groups table and use this value for
insertion.
Assuming that the supplier_groups table contains the following data:

5
1 INSERT INTO supplier_groups(group_name)
2 VALUES('One-time Supplier');
3
4 INSERT INTO supplier_groups(group_name)
5 VALUES('Third-party Supplier');
6
7 INSERT INTO supplier_groups(group_name)
8 VALUES('Inter-co Supplier');
9
10 SELECT
11 *
12 FROM
13 supplier_groups;

To insert a new third-party supplier, you have to use the group_id 2 as

follows:
1 INSERT INTO suppliers(supplier_name, group_id)
2 VALUES('Toshiba',1);
It works perfectly fine. However, the following statement also works:

1 INSERT INTO suppliers(supplier_name, group_id)

2 VALUES('WD',4);
The supplier_groups table has no row with group id 4 but nothing prevents
you from inserting it into the suppliers table, which is an issue.
For example, the following query fails to get all suppliers and their groups:

1 SELECT
2 supplier_name,
3 group_name
4 FROM
5 suppliers
6 INNER JOIN supplier_groups
7 USING(group_id);

As you can see, the WD supplier is missing in the result set.

6
A solution to fix this problem is to use the Oracle foreign key constraint to
enforce the relationship between rows from
the supplier_groups and suppliers tables.
First, drop the suppliers table:
1 DROP TABLE suppliers;
Second, recreate the suppliers table with a foreign key constraint:
1 CREATE TABLE suppliers (
2 supplier_id NUMBER,
3 supplier_name VARCHAR2(255) NOT NULL,
4 group_id NUMBER NOT NULL,
5 PRIMARY KEY(supplier_id),
6 FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)
7 );
In this statement, the following clause was newly added:

1 FOREIGN KEY(group_id) REFERENCES supplier_groups(group_id)

This clause defines the group_id column in the suppliers table as a foreign
key that references to the group_id column of the supplier_groups table.
This way, the constraint is enforced by Oracle. In other words, attempting to
insert a row into the suppliers table that does not correspond to any row in
the supplier_groups table will fail, as attempting to delete a row from
the supplier_groups table where there exist dependent rows in
the suppliers table.
The suppliers table is called a child table while the supplier_groups is
referred to as a parent table. To extend the parent-child analogy, the
primary key value is taken from the parent table (supplier_groups) and is
inserted into the child table (suppliers) i.e., the child carries a FOREIGN
KEY as the parent’s DNA or genetic code.
By the way, the concept of referential integrity is all about maintaining and
enforcing this parent-child relationship.

Oracle foreign key constraint in actions

The following statement works because the supplier_groups table has a row
with group_id 1:
1 INSERT INTO suppliers(supplier_name, group_id)
2 VALUES('Toshiba',1);
However, the following statement will fail:

1 INSERT INTO suppliers(supplier_name, group_id)

2 VALUES('WD',4);

7
Because the supplier_groups has no row with id 4. The following is the error
message:
SQL Error: ORA-02291: integrity constraint (OT.SYS_C0010646) violated -
1
parent key not found
Similarly, attempting to delete a row with group_id 1 in
the supplier_groups table will fail:
1 DELETE
2 FROM
3 supplier_groups
4 WHERE
5 group_id = 1;
Oracle issued the following error message:

SQL Error: ORA-02292: integrity constraint (OT.SYS_C0010654) violated -

1
child record found
Because the suppliers table (child table) has a row that references to the
row which is being deleted.
Oracle foreign key constraint syntax

Oracle allows you to create, add, drop, disable and enable a foreign key
constraint.

Create a foreign key constraint

The following statement illustrates the syntax of creating a foreign key

constraint when you create a table:

1 CREATE TABLE child_table (

2 ...
3 CONSTRAINT fk_name
4 FOREIGN KEY(col1, col2,...) REFERENCES parent_table(col1,col2)
5 ON DELETE [ CASCADE | SET NULL ]
6 );
Let’s examine the statement in detail.

First, to explicitly assign the foreign key constraint a name, you use
the CONSTRAINT clause followed by the name. The CONSTRAINT clause is
optional. If you omit it, Oracle will assign a system-generated name to the
foreign key constraint.
Second, specify the FOREIGN KEY clause defines one or more column as a
foreign key and parent table with columns to which the foreign key columns
reference.

8
Third, use the ON DELETE clause to specify consequence when the rows in
the parent table are deleted.

 ON DELETE CASCADE: if a row in the parent is deleted, then all the

rows in the child table that reference to that row will be deleted.
 ON DELETE SET NULL: if a row in the parent is deleted, then all the
rows in the child table reference to that row will be set to NULL for the
foreign key columns.
Unlike the primary key constraint, a table may have more than one foreign
key constraint.

Add a foreign key constraint to a table

If you want to add a foreign key constraint to an existing table, you use
the ALTER TABLE statement as follows:
1 ALTER TABLE child_table
2 ADD CONSTRAINT fk_name
3 FOREIGN KEY (col1,col2) REFERENCES parent_table(col1,col2);
Drop a foreign key constraint

To drop a foreign key constraint, you use the ALTER TABLE statement
below:
1 ALTER TABLE child_table
2 DROP CONSTRAINT fk_name;
Disable a foreign key constraint

To temporarily disable a foreign constraint, you use the following ALTER

TABLE statement:
1 ALTER TABLE child_table
2 DISABLE CONSTRAINT fk_name;
Enable a foreign constraint

Similarly, you use also use the ALTER TABLE statement to enable a disabled
foreign key constraint:
1 ALTER TABLE child_table
2 ENABLE CONSTRAINT fk_name;

Oracle unique constraint syntax

A unique constraint is an integrity constraint that ensures the data stored in
a column, or a group of columns, is unique among the rows in a table.

9
Typically, you apply the unique constraints to columns when you create the
table using the inline constraint syntax as follows:

1 CREATE TABLE table_name (

2 ...
3 column_name data_type UNIQUE
4 ...
5 );

This unique constraint specifies that the values in the column_name is

unique across the whole table.
You can also use the out-of-line constraint syntax to define a unique
constraint:

1 CREATE TABLE table_name (

2 ...,
3 UNIQUE(column_name)
4 );

It’s possible to assign a unique constraint a name by using

the CONSTRAINT clause followed by the constraint name:
CREATE TABLE table_name (
1
...
2
column_name data_type CONSTRAINT unique_constraint_name UNIQU
3
E
4
...
5
);

or with out-of-line constraint syntax:

1 CREATE TABLE table_name (

2 ...
3 column_name data_type,

10
4 ...,
5 CONSTRAINT unique_constraint_name UNIQUE(column_name)
6 );

To define a unique constraint for a group of columns, you use the out-of-line
constraint syntax:

CREATE TABLE table_name (

1
...
2
column_name1 data_type,
3
column_name2 data_type,
4
...,
5
CONSTRAINT unique_constraint_name UNIQUE(column_name1, column
6
_name2)
7
);

This specifies that the combination of values in

the column_name1 and column_name2 is unique across the whole table,
though any one of these columns need not be unique.
If you want to add a unique constraint to an existing table, you use
the ALTER TABLE statement:
ALTER TABLE table_name
1
ADD CONSTRAINT unique_constraint_name UNIQUE(column_name1, colu
2
mn_nam2);

Sometimes, you may want to disable a unique constraint temporarily:

1 ALTER TABLE table_name

2 DISABLE CONSTRAINT unique_constraint_name;

And then enable it:

1 ALTER TABLE table_name

2 ENABLE CONSTRAINT unique_constraint_name;

Or even drop a unique constraint:

11
1 ALTER TABLE table_name
2 DROP CONSTRAINT unique_constraint_name;

Oracle unique constraint examples

Let’s create a table named clients for the demonstration:

1 CREATE TABLE clients (
2 client_id NUMBER,
3 first_name VARCHAR2(50) NOT NULL,
4 last_name VARCHAR2(50) NOT NULL,
5 company_name VARCHAR2(255) NOT NULL,
6 email VARCHAR2(255) NOT NULL UNIQUE,
7 phone VARCHAR(25)
8 );

The email column has a unique constraint that ensures there will be no
duplicate email.
The following statement inserts a row into the clients table:
INSERT INTO clients(first_name,last_name, email, company_name, phone
1)
2 VALUES('Christene','Snider','[email protected]', 'ABC Inc', '408-87
5-6075');

Now, we attempt to insert a new row whose email value already exists in
the email column:
INSERT INTO clients(first_name,last_name, email, company_name, phone
1)
2 VALUES('Sherly','Snider','[email protected]', 'ABC Inc', '408-875-6
076');

Oracle issued the following error message indicated that the unique
constraint has been violated:

1 SQL Error: ORA-00001: unique constraint (OT.SYS_C0010726) violated

12
If you want to add a unique constraint for
the company_nameand phone columns, you can use the following ALTER
TABLE statement:
ALTER TABLE clients
1
ADD CONSTRAINT unique_company_phone UNIQUE(company_name, phon
2
e);

As the result, the combination of values in

the company_name and phone columns is unique among the rows in
the clients table.
The following statement attempts to insert a new client with the company
and phone that already exist:

INSERT INTO clients(first_name,last_name, email, company_name, phone

1)
2 VALUES('Sherly', 'Snider','[email protected]', 'ABC Inc', '408-875-6
075');

Oracle issued the following error message:

SQL Error: ORA-00001: unique constraint (OT.UNIQUE_COMPANY_PHONE)

1
violated

However, you can add the client who is in the company that already exists in
the clients table but has a different phone:
INSERT INTO clients(first_name,last_name, email, company_name, phone
1)
2 VALUES('Sherly','Snider','[email protected]', 'ABC Inc', '408-875-607
6');

To disable the unique constraint UNIQUE_COMPANY_PHONE, you use the

following statement:
1 ALTER TABLE clients
2 DISABLE CONSTRAINT unique_company_phone;

And to enable it:

1 ALTER TABLE clients

2 ENABLE CONSTRAINT unique_company_phone;

13
Or to drop it permanently:

1 ALTER TABLE clients

2 DROP CONSTRAINT unique_company_phone;

In this tutorial, you have learned how to use the Oracle unique constraint to
ensure the data contained in a column or a group of columns is unique
among the rows in the table.

Oracle Check Constraint

This tutorial, you will learn how to use the Oracle check constraint to enforce
domain integrity.

Introduction to Oracle Check constraint

An Oracle check constraint allows you to enforce domain integrity by limiting

the values accepted by one or more columns.

To create a check constraint, you define a logical expression that returns

true or false. Oracle uses this expression to validate the data that is being
inserted or updated. If the expression evaluates to true, Oracle accepts the
data and carry the insert or update. Otherwise, Oracle will reject the data
and does not insert or update at all.

You can apply a check constraint to a column or a group of columns. A

column may have one or more check constraints.

When you apply multiple check constraints to a column, make sure that they
are not mutually exclusive. In addition, you should not assume any
particular order of evaluation of the expressions.

Creating Check constraint syntax

Typically, you create a check constraint on a column when you create the
table:

1 CREATE TABLE table_name (

2 ...
3 column_name data_type CHECK (expression),
4 ...

14
5 );
In this syntax, a check constraint consists of the keyword CHECK followed by
an expression in parentheses. The expression should always involve the
column thus constrained. Otherwise, the check constraint does not make any
sense.
If you want to assign the check constraint an explicit name, you use
the CONSTRAINT clause below:
1 CONSTRAINT check_constraint_name
2 CHECK (expression);
When a check constraint is on the same line as the table column, its syntax
is called in-line constraint.

In addition, you can use the out-of-line constraint syntax as follows:

1 CREATE TABLE table_name (

2 ...,
3 CONSTRAINT check_constraint_name CHECK (expresssion)
4 );
Creating Oracle Check constraint examples

The following example creates the parts table whose buy prices are positive:
1 CREATE TABLE parts (
2 part_id NUMBER,
3 part_name VARCHAR2(255) NOT NULL,
4 buy_price NUMBER(9,2) CHECK(buy_price > 0),
5 PRIMARY KEY(part_id)
6 );
Attempting to insert 0 or negative buy price will cause an error:

1 INSERT INTO parts(part_name, buy_price)

2 VALUES('HDD',0);
Oracle issued the following error:

1 SQL Error: ORA-02290: check constraint (OT.SYS_C0010681) violated

In this error message, SYS_C0010681 is the name of the check constraint
assigned by Oracle and OT is the schema name.
To better analyzing the error message and to refer to the constraint later,
you can give a check constraint an explicit name:

1 DROP TABLE parts;

15
2
3 CREATE TABLE parts (
4 part_id NUMBER,
5 part_name VARCHAR2(255) NOT NULL,
6 buy_price NUMBER(9,2) CONSTRAINT check_positive_buy_price CHECK
7 (buy_price > 0),
8 PRIMARY KEY(part_id)
);
Now, if you try to insert a part with a negative price:

1 INSERT INTO parts(part_name, buy_price)

2 VALUES('Screen',-100);
The error message is more precise:

SQL Error: ORA-02290: check constraint (OT.CHECK_POSITIVE_BUY_PRIC

1
E) violated
Add Check constraint to a table

To add a check constraint to an existing table, you use the ALTER TABLE
ADD CONSTRAINT statement as follows:
1 ALTER TABLE table_name
2 ADD CONSTRAINT check_constraint_name CHECK(expression);
For example, the following statement adds the cost column to
the parts table:
1 ALTER TABLE parts
2 ADD cost NUMBER(9,2);
Suppose the cost must be positive and it is also greater or equal to the buy
price. To enforce these rules, you add two check constraints to
the parts table:
1
2 ALTER TABLE parts
3 ADD CONSTRAINT check_positive_cost CHECK (cost > 0);
4
5
Drop checking constraint

To drop a check constraint, you use the ALTER TABLE DROP

CONSTRAINT statement as follows:
1 ALTER TABLE table_name
2 DROP CONSTRAINT check_constraint_name;

16
For example, to drop the check_valid_cost constraint, you use the following
statement:
1 ALTER TABLE parts
2 DROP CONSTRAINT check_valid_cost;
Disable / Enable check constraint

The following statements disable and enable a check constraint:

1 ALTER TABLE table_name

2 DISABLE CONSTRAINT check_constraint_name;
3
4 ALTER TABLE table_name
5 ENABLE CONSTRAINT check_constraint_name;
For example, to temporarily disable the check_positive_buy_price constraint,
you use the following statement:
1 ALTER TABLE table_name
2 DISABLE CONSTRAINT check_positive_buy_price;
And to enable the check_positive_buy_price constraint:
1 ALTER TABLE table_name
2 ENABLE CONSTRAINT check_positive_buy_price;
Restrictions of Oracle check constraint

Oracle check constraints are subject to the following restrictions:

1. You can define check constraints for tables only, not views.
2. The expression of the check constraint can refer to any column in the
table, but it cannot refer to columns of other tables.
3. The expression also cannot contain one of the following constructs:
o Non-deterministic functions such as SYSDATE, CURRENT_DATE,
and CURRENT_TIMESTAMP.
o Subqueries or a scalar subquery expressions.

o Calls to any user-defined functions.

o Nested table columns or attributes.
o The pseudo-columns CURRVAL, NEXTVAL, LEVEL, or ROWNUM.
o Date constants that are not fully specified.

Oracle NOT NULL

This tutorial, you will learn how to use the Oracle NOT NULL constraint to
enforce a column not to accept NULL values.

17
An Oracle NOT NULL constraint specifies that a column cannot
contain NULL values. The Oracle NOT NULL constraints are inline constraints
which are typically used in the column definition of the CREATE
TABLE statement.
1 CREATE TABLE table_name (
2 ...
3 column_name data_type NOT NULL
4 ...
5 );

It is possible to add a NOT NULL constraint to an existing table by using

the ALTER TABLE statement.
1 ALTER TABLE table_name MODIFY ( column_name NOT NULL);

In this case, the column_name must not contain any NULL value before
applying the NOT NULL constraint.
Oracle NOT NULL constraint examples
The following statement creates the surcharges table:
1 CREATE TABLE surcharges (
2 surcharge_id NUMBER,
3 surcharge_name VARCHAR2(255) NOT NULL,
4 amount NUMBER(9,2),
5 PRIMARY KEY (surcharge_id)
6 );

The surcharges table has three columns: surcharge id, surcharge name, and
amount.
The surcharge_id column is the primary key column of the table specified by
the PRIMARY KEY constraint, therefore, Oracle implicitly adds a NOT
NULL constraint to this column.
The surcharge_name column has a NOT NULL constraint specified explicitly
in the column definition.
The amount column can accept NULL values.
The following statement inserts a row into the surcharges table:
1 INSERT INTO surcharges(surcharge_name, amount)
2 VALUES('Late order placement',10);

It works as expected.

18
However, the following statement does not work:

1 INSERT INTO surcharges(surcharge_name, amount)

2 VALUES(null,20);

Because it attempts to insert a NULL value into the surcharge column which
has a NOT NULL constraint.
The following statement works because the amount column accepts NULL
values:

1 INSERT INTO surcharges(surcharge_name, amount)

2 VALUES('Rush Order',NULL);

The following statement displays all constraints of the surcharges table:

1 SELECT
2 table_name,
3 constraint_name,
4 search_condition
5 FROM
6 user_constraints
7 WHERE
8 table_name = 'SURCHARGES';

If you want to add a NOT NULL constraint to the amount column, you use
the following ALTER TABLE statement:
1 ALTER TABLE surcharges MODIFY (amount NOT NULL);

The following error occurred:

1 SQL Error: ORA-02296: cannot enable (OT.) - null values found

Because the surcharges table contains a NULL value.

19
So before adding the NOT NULL constraint, you need to make sure that the
existing data in the surcharges table does not violate the NOT
NULL constraint:
1 UPDATE
2 surcharges
3 SET
4 amount = 0
5 WHERE
6 amount IS NULL;

Now, if you execute the ALTER TABLE statement again:

1 ALTER TABLE surcharges MODIFY (amount NOT NULL);

It should work as expected.

Drop NOT NULL constraints

Sometimes, you need to change a column with a NOT NULL constraint to
accept NULL values.
To do this, you need to remove the NOT NULL constraint from the column by
using the ALTER TABLE statement as below:
1 ALTER TABLE table_name MODIFY ( column_name NULL)

For example, to drop the NOT NULL constraint from the amount column of
the surcharges table, you use the following statement:
1 ALTER TABLE surcharges
2 MODIFY (amount NULL);

20