Adding a Constraint with CHECK

Constraints and Their Meaning:

ConstraintConstraint TypeMeaning
CHECKCThe value for a column, or group of columns, must satisfy a certain condition.
NOT NULLCThe column cannot store a null value.
PRIMARY KEYPThe primary key of a table.
FOREIGN KEYRA foreign key for a table.
UNIQUEUThe column, or group of columns, can store only unique values.
CHECK OPTIONVChanges to the table rows made through a view must pass a check first.
READ ONLYOThe 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

DatatypeNOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK
TIMESTAMP WITH TIME ZONEAllowedNot AllowedNot AllowedNot AllowedAllowed
BLOBAllowedNot AllowedNot AllowedNot AllowedAllowed
CLOBAllowedNot AllowedNot AllowedNot AllowedAllowed
Home »
Oracle »
Table » 

Constraints:
  1. Adding a Constraint with CHECK
  2. Adding a NOT NULL Constraint
  3. Adding a FOREIGN KEY Constraint
  4. ON DELETE CASCADE
  5. ON DELETE SET NULL
  6. Adding a UNIQUE Constraint
  7. CHECK constraint
  8. Multiple Constraints
  9. Dropping a Constraint
  10. Disabling a Constraint
  11. Enabling a Constraint
  12. Deferred Constraints
  13. Getting Information on Constraints:user_constraints and all_constraints
Related: