Adding a Constraint
Constraint Constraint Type Meaning
CHECK C Specifies the value for a column, or group of columns, must satisfy a certain condition.
NOT NULL C Specifies a column doesn't allow storage of null values. This is actually enforced as a CHECK constraint. You can check NOT NULL columns using the DESCRIBE command.
PRIMARY KEY P Specifies the primary key of a table. A primary key is made up of one or more columns that uniquely identify each row in a table.
FOREIGN KEY R Specifies a foreign key for a table. A foreign key references a column in another table, or a column in the same table in the case of a self-reference.
UNIQUE U Specifies a column, or group of columns, can only store unique values.
CHECK OPTION V Specifies that DML operations on a view must satisfy the subquery.
READ ONLY O Specifies that a view may only be read from.
Adding a CHECK Constraint
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_status_ck
CHECK (status IN ('PLACED', 'PENDING', 'SHIPPED'));
INSERT INTO order_status2 (
id, status, last_modified, modified_by)
VALUES (
1, 'PENDING', '01-JAN-2005', 1
);
INSERT INTO order_status2 (
id, status, last_modified, modified_by
) VALUES (
2, 'CLEARED', '01-JAN-2005', 2
);
INSERT INTO order_status2 (
*
ERROR at line 1:
ORA-02290: check constraint (STORE.ORDER_STATUS2_STATUS_CK) violated
You can use other comparison operators with a CHECK constraint. The next example adds a CHECK constraint that enforces the id value is greater than zero:
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_id_ck CHECK (id > 0);
To add a constraint, any existing rows in the table must satisfy the constraint.
Adding a NOT NULL Constraint
ALTER TABLE order_status2
MODIFY status CONSTRAINT order_status2_status_nn NOT NULL;
Notice you use MODIFY to add a NOT NULL constraint rather than ADD CONSTRAINT. The next example adds a NOT NULL constraint on the modified_by column:
ALTER TABLE order_status2
MODIFY modified_by CONSTRAINT order_status2_modified_by_nn NOT NULL;
The next example adds a NOT NULL constraint on the last_modified column:
ALTER TABLE order_status2
MODIFY last_modified NOT NULL;
Adding a FOREIGN KEY Constraint
ALTER TABLE order_status2
DROP COLUMN modified_by;
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_modified_by_fk
modified_by REFERENCES employees(employee_id);
ALTER TABLE order_status2
DROP COLUMN modified_by;
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_modified_by_fk
modified_by REFERENCES employees(employee_id) ON DELETE CASCADE;
You use the ON DELETE SET NULL clause with a FOREIGN KEY constraint to specify that when a row in the parent table is deleted, the foreign key column for the row (or rows) in the child table is set to null. The following example drops the modified_by column from order_status2 and rewrites the previous example to include the ON DELETE SET NULL clause:
ALTER TABLE order_status2
DROP COLUMN modified_by;
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_modified_by_fk
modified_by REFERENCES employees(employee_id) ON DELETE SET NULL;
ALTER TABLE order_status2
DROP COLUMN modified_by;
Adding a UNIQUE Constraint
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_status_uq UNIQUE (status);
Dropping a Constraint
ALTER TABLE order_status2
DROP CONSTRAINT order_status2_status_uq;
Disabling a Constraint
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_status_uq UNIQUE (status) DISABLE;
ALTER TABLE order_status2
DISABLE CONSTRAINT order_status2_status_nn;
You can add CASCADE to the end of a DISABLE CONSTRAINT clause to disable any constraints that depend on the specified constraint. You must use CASCADE when you disable a primary key or unique constraint that is part of a foreign key constraint.
Enabling a Constraint
ALTER TABLE order_status2
ENABLE CONSTRAINT order_status2_status_uq;
You can also choose to apply a constraint to new data only by specifying ENABLE NOVALIDATE. For example:
ALTER TABLE order_status2
ENABLE NOVALIDATE CONSTRAINT order_status2_status_uq;
The default is ENABLE VALIDATE
Deferred Constraints
A deferred constraint is one that is enforced when a transaction is committed. You specify a constraint is deferrable using the DEFERRABLE clause when you initially add a constraint. Once you’ve added a constraint, you cannot change it to DEFERRABLE ; instead, you must drop and recreate the constraint.
When you add a DEFERRABLE constraint, you can mark it as INITIALLY IMMEDIATE or INITIALLY DEFERRED. INITIALLY IMMEDIATE means that the constraint is checked whenever you add, update, or delete rows from a table (this is the same as the default behavior of a constraint). INITIALLY DEFERRED means that the constraint is only checked when a transaction is committed. Let’s take a look at an example.
The following statement drops the order_status2_status_uq constraint:
ALTER TABLE order_status2
DROP CONSTRAINT order_status2_status_uq;
The next example adds the order_status2_status_uq constraint, setting it to DEFERRABLE INITIALLY DEFERRED:
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_status_uq UNIQUE (status)
DEFERRABLE INITIALLY DEFERRED;
If you add rows to order_status2, the order_status2_status_uq constraint isn’t enforced until you perform a commit.
Getting Information on Constraints
Column Type Description
owner VARCHAR2(30) Owner of the constraint.
constraint_name VARCHAR2(30) Name of the constraint.
constraint_type VARCHAR2(1) Constraint type. Set to P, R, C, U, V, or O
table_name VARCHAR2(30) Name of the table on which the constraint is defined.
status VARCHAR2(8) Constraint status. Set to ENABLED or DISABLED.
deferrable VARCHAR2(14) Whether the constraint is deferrable. Set to DEFERRABLE or NOT DEFERRABLE.
deferred VARCHAR2(9) Whether the deferred. Set to IMMEDIATE or DEFERRED.
You can get information on all the constraints you have access to using all_constraints
SELECT
constraint_name, constraint_type, status, deferrable, deferred
FROM user_constraints
WHERE table_name = 'ORDER_STATUS2';
Getting Information on the Constraints on a Column
Column Type Description
owner VARCHAR2(30) Owner of the constraint.
constraint_name VARCHAR2(30) Name of the constraint.
table_name VARCHAR2(30) Name of the table on which the constraint is defined.
column_name VARCHAR2(4000) Name of the column on which the constraint is defined.
COLUMN column_name FORMAT a15
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'ORDER_STATUS2';
The next example joins user_constraints and user_cons_columns to get the column_name , constraint_name, constraint_type, and status:
SELECT
ucc.column_name, ucc.constraint_name, uc.constraint_type, uc.status
FROM user_constraints uc, user_cons_columns ucc
WHERE uc.table_name = ucc.table_name
AND uc.constraint_name = ucc.constraint_name
AND ucc.table_name = 'ORDER_STATUS2';
