Monday , September 23 2019
Home / SQL / Definition and Types of SQL Constraints in Oracle

Definition and Types of SQL Constraints in Oracle

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.

Syntax:

CREATE TABLE table_name

(

column_name_1     datatype (length)   CONSTRAINT    constraint_name    constraint_type,

column_name_2    datatype (length),

column_name_3    datatype (length)

);

Here,

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.

Syntax:

ALTER    TABLE     table_name    ADD   CONSTRAINT constraint_name    constraint_type  (constraint_column_name);

Here,

constraint_column_name is the name of the column for which you are setting the constraint.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

One comment

  1. I believe this internet site contains some rattling superb info for everyone. “Billy T-T-T-T-Today, Junior” by Billy Madison.

Leave a Reply

Your email address will not be published. Required fields are marked *