ORACLE: Difference between Inline vs. Outline/Out-of-line Constraints.

The difference between the Inline and Outline constraint is quite simple.

The Inline constraints which are column level constraints can only be defined at the time of table creation.

The Outline/Out of Line constraints can be define at the time of creation of table as well as after table creation, so that constraints can be placed later by altering the structure of the table.

The table level constraints serve the same purpose as column level constraints and there is no significance in terms of handling and managing constraints either Inline or Outline.

Following is the examples for inline and outlined constraints.

--Inline Constraints

CREATE TABLE AXY
   (empno        NUMBER         CONSTRAINT pk_empno PRIMARY KEY(empno),
    ename        VARCHAR2(1000) CONSTRAINT not_null_ename NOT NULL,
    gender       VARCHAR2(1)    CONSTRAINT chk_constraint CHECK (gender IN('M','F')));

 

OR

CREATE TABLE AXY

   (empno       NUMBER         PRIMARY KEY(empno),
    ename       VARCHAR2(1000) NOT NULL,
    gender      VARCHAR2(1)    CHECK (gender IN('M','F')));

--Outline/Out of the Line Constraints

CREATE TABLE AXY

   (empno       NUMBER,
    ename       VARCHAR2(1000),
    gender      VARCHAR2(1),
    CONSTRAINT  pk_empno PRIMARY KEY(EMPNO),
    CONSTRAINT  not_null_ename     NOT NULL,
    CONSTRAINT  chk_constraint 
    CHECK      (gender IN('M','F')));

OR

CREATE TABLE AXY

   (empno    NUMBER,
    ename    VARCHAR2(1000),
    gender   VARCHAR2(1));


ALTER TABLE AXY ADD CONSTRAINT pk_empno PRIMARY KEY(empno);
ALTER TABLE AXY ADD CONSTRAINT not_null_ename   NOT NULL;
ALTER TABLE AXY ADD CONSTRAINT chk_constraint  CHECK (gender IN('M','F'));

Another Example. for Inline and outline out of the line constraints.

Inline Constraints observe the department_id Constraints how it is different then the Outline Constraints as given below.

CREATE TABLE dept_20 
   (employee_id     NUMBER(4), 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4), 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id   CONSTRAINT fk_deptno 
                    REFERENCES departments(department_id) ); 

Outline/Out of the line Constraint.

CREATE TABLE dept_20 
   (employee_id     NUMBER(4), 
    last_name       VARCHAR2(10), 
    job_id          VARCHAR2(9), 
    manager_id      NUMBER(4), 
    hire_date       DATE, 
    salary          NUMBER(7,2), 
    commission_pct  NUMBER(7,2), 
    department_id, 
   CONSTRAINT fk_deptno 
      FOREIGN  KEY (department_id) 
      REFERENCES  departments(department_id) );