Oracle Integrity Constraints Syntax

Integrity constraints can be defined in two ways.

Inline, as part of an individual column definition.
Out-of-line, as part of the table definition.

Both these techniques have been used previously in this section.

Inline Constraints

Here are some examples of inline constraints:

CREATE TABLE continent (
  cont_id    NUMBER        PRIMARY KEY,
  cont_name  VARCHAR2(20)  NOT NULL UNIQUE
);

CREATE TABLE country (
  ctry_id         NUMBER        PRIMARY KEY,
  cont_id         NUMBER        NOT NULL REFERENCES continent,
  ctry_name       VARCHAR2(50)  NOT NULL UNIQUE,
  ctry_area       NUMBER        NOT NULL CHECK (ctry_area > 0),
  ctry_pop        NUMBER        NOT NULL CHECK (ctry_pop > 0),
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);

Each constraint is defined entirely as part of the column definition. Remember that primary key implies NOT NULL so there is no need to specify this explicitly.

Out-Of-Line Constraints

Here are the same constraints defined out-of-line:

CREATE TABLE continent (
  cont_id    NUMBER,
  cont_name  VARCHAR2(20)  NOT NULL,
	PRIMARY KEY (cont_id),
	UNIQUE (cont_name)
);

CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL,
	PRIMARY KEY (ctry_id),
	FOREIGN KEY (cont_id) REFERENCES continent,
	UNIQUE (ctry_name),
	CHECK (ctry_area > 0),
	CHECK (ctry_pop > 0)
);

These constraints are now part of the table definition rather the column definition.

Which syntax you use is purely a matter of taste, however there are a couple restrictions that must be taken into consideration.

NOT NULL constraints must be declared inline.
Constraints that reference more than one column must be decalred out-of-line.

For example, both these out-of-line constraints reference multiple columns.

CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL,
	UNIQUE (cont_id, ctry_name),
	CHECK (ctry_pop > ctry_area)
);

Constraint Names

Constraints can also be named by adding the CONSTRAINT keyword to either inline or out-of-line definitions.

CREATE TABLE continent (
  cont_id    NUMBER        CONSTRAINT continent_pk PRIMARY KEY,
  cont_name  VARCHAR2(20)  CONSTRAINT cont_name_nn NOT NULL
                           CONSTRAINT continent_uk1 UNIQUE
);

CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL,
	CONSTRAINT county_fk1 FOREIGN KEY (cont_id) REFERENCES continent,
	CONSTRAINT county_uk1 UNIQUE (ctry_name),
	CONSTRAINT county_cc1 CHECK (ctry_area > 0),
	CONSTRAINT county_cc2 CHECK (ctry_pop > 0)
);

If the name is not specified then Oracle will assign a unique name to the constraint such as SYS_C0014607 or SYS_C0014619.

quick start