SQL Advanced

Constraints

- by OracleWorld 232
Post Views: 232

To enforce data integrity in a database, Oracle provides us with “constraints”.  We recognize 5 constraints:

  • NOT NULL
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK

You can use constraints in the following statements:

  • CREATE / ALTER TABLE
  • CREATE / ALTER VIEW

There are two ways of how you can apply a constraint: inline and outline specification. The first one is defined as a part of a definition of a single column or attribute. The latter is defined as a part of a table definition.

I will now explain and demonstrate how those constraints are used and how can they help you

NOT NULL

By using this constraint, you specify that a particular column cannot contain NULL values. If you do not specify it, it will behave the same as if you specify NULL; thus, it will be allowed to store NULL values in that particular column.

-- INLINE DEFINITION:    
CREATE TABLE my_table (
id INT NOT NULL,
value VARCHAR2(50)
);

-- OUTLINE DEFINITION:
CREATE TABLE my_table (
id INT,
value VARCHAR2(50)
);
ALTER TABLE my_table ADD CONSTRAINT constraint_name NOT NULL (id); 

/*
Note: If you inserted NULL values before creating this constraint, you will have to fix them or it will throw you an error. If you want to circumvent this feature, you have to define NOVALIDATE parameter at the end of the command above. That means, the NOT NULL constraint will apply on newly inserted and updated rows. Note, that you will not be able to update rows that does not satisfy the constraint condition. Moreover, optimizer will ignore the constraint while building a plan. 
*/


INSERT INTO my_table (id, value) VALUES (1, 'Oracle World');
-- 1 row inserted; OK


INSERT INTO my_table (id, value) VALUES (NULL, 'Oracle World');
-- ORA-01400: cannot insert NULL into (...)


INSERT INTO my_table (value) VALUES ('Oracle World');
-- ORA-01400: cannot insert NULL into (...)

PRIMARY KEY

The PRIMARY KEY constraint designates a column in a table as a primary key. When there are multiple columns involved, we are talking about COMPOSITE PRIMARY KEY. The primary key is a combination of NOT NULL and UNIQUE constraints at once.

There are some restrictions on the primary key creation:

  • there can be only one primary key in a table
  • the primary key cannot be set over a specified set of data types (i.e. LOB, LONG, VARRAY, NESTED TABLE, …)
  • cannot have more than 32 columns

FOREIGN KEY

The FOREIGN KEY is, in fact, a reference to a primary key. The table containing the PRIMARY KEY is called “parent table” and the one with the foreign key is called “child table” (obviously 🙂 ). Once the foreign key is defined you cannot insert other value that is already presented in the column/columns of a primary key. Unlike with a primary key, you can define multiple foreign keys in a single table and you can refer to a parent table from multiple child tables.

Not only you can enforce the data integrity while inserting with the foreign key but you can also enforce it while updating/deleting the data with the clause ON DELETE. If omitted, you will not be able to delete records in the parent table. When you use ON DELETE, anytime you update/delete the primary key in the parent table something will happen with the data in a child table (foreign key column). This is called CASCADING. There are two types of cascading:

  • CASCADE – this will delete related records in child table/tables
  • SET NULL – this will set to NULL related records in child table/tables

UNIQUE

The UNIQUE constraint ensures uniqueness in a defined column or set of columns (= in case of multiple columns, we are talking about “composite unique key” (restriction: no more than 32 columns can be used); You cannot declare composite unique key inline).  The inline definition requires you to specify UNIQUE keyword after the column name and data type specification, whereas the outline definition requires you to specify UNIQUE and one or more columns where that constraint should be applied.

When a UNIQUE constraint is applied, no two rows cannot have the same value (please note that ‘VALUE’ and ‘VALUE ‘ are not the same values; mind that white space at the end). If NULL value is inserted, the UNIQUE constraint is ignored. That means you can have “duplicates” with NULL values.

When COMPOSITE UNIQUE KEY is applied, no two sets of columns cannot have the same value. If all columns contain NULL values, the unique constraint will be ignored. However, when only one of them will contain NULL value, the unique constraint will be violated.

Every time you create a unique constraint, Oracle will automatically create an index on that column/set of columns.

-- INLINE DEFINITION:    
CREATE TABLE my_table (
id INT UNIQUE,
value VARCHAR2(50)
);

-- OUTLINE DEFINITION:
CREATE TABLE my_table (
id INT,
value VARCHAR2(50)
);
ALTER TABLE my_table ADD CONSTRAINT constraint_name UNIQUE (id);
-- for composite: ALTER TABLE my_table ADD CONSTRAINT constraint_name_composite UNIQUE (id, value);

INSERT INTO my_table (id, value) VALUES (1, 'Oracle World');
-- 1 row inserted; OK
INSERT INTO my_table (id, value) VALUES (1, 'Another String');
-- ORA-00001: unique constraint violated ...
INSERT INTO my_table (id, value) VALUES (NULL, 'Another String');
-- 1 row inserted; OK

CHECK

The CHECK constraint allows you to check the value before it is inserted in a table. If the condition is met, the value is inserted otherwise it will throw an error. In the case of NULL values – it will always be evaluated as true.

-- inline
CREATE TABLE my_table (
id INT,
value VARCHAR2(50),
CONSTRAINT constraint_name CHECK (value IN ('A','B','C')) -- only A,B,C or NULL will be accepted
);

-- outline
CREATE TABLE my_table (
id INT,
value VARCHAR2(50)
);
ALTER TABLE my_table ADD CONSTRAINT constraint_name  CHECK (value IN ('A','B','C'));


-- disable constraint
ALTER TABLE my_table DISABLE CONSTRAINT constraint_name;

-- enable constraint
ALTER TABLE my_table ENABLE CONSTRAINT constraint_name;

-- drop constraint
ALTER TABLE my_table DROP CONSTRAINT constraint_name;