Constraint is a specific rule to data. The rule restricts data to be inserted or updated into the table in oracle database. Data must be conformed the rule before inserting and updating into table. So, constraint is used to set specific rules on column of a table. When we insert or update data for the column, data or values should be met the rules.
The commonly used constraints are described briefly below.
- NOT NULL : The NOT NULL constraint is used to restrict null values to be inserted.
- UNIQUE : The UNIQUE constraint prohibits duplicate values to be inserted into the same column.
- PRIMARY KEY : The PRIMARY KEY allows to set both NOT NULL and UNIQUE constraint in a single declaration. In result, it prohibits null values and duplicate values to be inserted into the column. When we set PRIMARY KEY constraint on a table column, the table will be known as parent table.
- FORIEGN KEY : The FORIEGN KEY constraint is used to set a rule that matches values with the referenced or parent table before inserting or updating data in child table column. When we set a FORIEGN KEY constraint on the table column, the table will be known as child table. The data or values must have in parent table column to be inserted or updated in child table column.
- CHECK : The CHECK constraint sets specific condition on the column that must be met to insert data.
We can define constraint in two different ways.
- Inline : The inline specification is as part of individual column definition.
- Out-Of-Line : The out-of-line is as part of table definition.
The constraint can be set in CREATE TABLE statement or ALTER TABLE statement.
You can set constraint when you create table by CREATE TABLE statement.
CREATE TABLE table_name
column_name_1 datatype (length) CONSTRAINT constraint_name constraint_type,
column_name_2 datatype (length),
column_name_3 datatype (length)
CONSTRAINT keyword is used to set a constraint on a single column or combination of columns.
constraint_name is the name of the constraint that will be named by oracle programmer or administrator. If you do not define the constraint name, then oracle will define a name as per system rules which are complex for programmer to remember. So, you should provide a constraint name during constraint creation.
constraint_type is the type of the constraint that may be PRIMARY KEY, UNIQUE, NOT NULL, CHECK.
You can set constraint after creating a table definition by ALTER TABLE statement. It will save time to define constraint on a table column that already exists without dropping and recreating table. So, we will be save from complexity. Otherwise we have to drop the table and recreate it together with the constraint. In result, the data may be lost during table dropping or you should first backup huge amount of data and restore it after table recreating the table. It will be much time consuming process. So we should define constraint after creating table by ALTER TABLE statement.
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (constraint_column_name);
constraint_column_name is the name of the column for which you are setting the constraint.