Adding a Constraint with CHECK

Constraints and Their Meaning:

Constraint	Constraint Type	Meaning
CHECK	C	The value for a column, or group of columns, must satisfy a certain condition.
NOT NULL	C	The column cannot store a null value.
PRIMARY KEY	P	The primary key of a table.
FOREIGN KEY	R	A foreign key for a table.
UNIQUE	U	The column, or group of columns, can store only unique values.
CHECK OPTION	V	Changes to the table rows made through a view must pass a check first.
READ ONLY	O	The read only.

The following ALTER TABLE statement adds a CHECK constraint to the emp table:

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  HIREDATE DATE,
                  DEPTNO NUMBER(2));

SQL> ALTER TABLE emp
  2  ADD CONSTRAINT my_ck
  3  CHECK (JOB IN ('Tester', 'Developer', 'Coder'));

Table altered.

The next example adds a CHECK constraint that enforces that the empno value is greater than zero:

SQL> ALTER TABLE emp
  2  ADD CONSTRAINT id_ck CHECK (empno > 0);

Table altered.

SQL>
SQL>

When adding a constraint, the existing rows in the table must satisfy the constraint.

Datatype Restrictions on constraints

Datatype	NOT NULL	UNIQUE	PRIMARY KEY	FOREIGN KEY	CHECK
TIMESTAMP WITH TIME ZONE	Allowed	Not Allowed	Not Allowed	Not Allowed	Allowed
BLOB	Allowed	Not Allowed	Not Allowed	Not Allowed	Allowed
CLOB	Allowed	Not Allowed	Not Allowed	Not Allowed	Allowed