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 |
Home »
Oracle »
Table »
Oracle »
Table »
Constraints:
- Adding a Constraint with CHECK
- Adding a NOT NULL Constraint
- Adding a FOREIGN KEY Constraint
- ON DELETE CASCADE
- ON DELETE SET NULL
- Adding a UNIQUE Constraint
- CHECK constraint
- Multiple Constraints
- Dropping a Constraint
- Disabling a Constraint
- Enabling a Constraint
- Deferred Constraints
- Getting Information on Constraints:user_constraints and all_constraints
Related: