Oracle SQL Constraints Cheat Sheet
1. Creating Constraints
Constraints can be added in two ways:
- Inline (column-level)
- Table-level
Example:
CREATE TABLE emp (
empno NUMBER PRIMARY KEY,
emp_name VARCHAR2(50) UNIQUE,
job VARCHAR2(20) CHECK (job IN ('Prof','AP')),
sal NUMBER NOT NULL,
deptno NUMBER,
CONSTRAINT fk_dept FOREIGN KEY (deptno) REFERENCES dept(deptno)
);
2. Add Constraint after table creation
ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (empno);
ALTER TABLE emp ADD CONSTRAINT uq_empname UNIQUE (emp_name);
ALTER TABLE emp ADD CONSTRAINT chk_salary CHECK (sal > 0);
ALTER TABLE emp ADD CONSTRAINT fk_dept FOREIGN KEY (deptno) REFERENCES
dept(deptno);
3. Drop Constraints
General syntax: ALTER TABLE table_name DROP CONSTRAINT constraint_name;
- Drop Primary Key: ALTER TABLE emp DROP PRIMARY KEY;
- Drop Foreign Key: ALTER TABLE emp DROP CONSTRAINT fk_dept;
- Drop Unique: ALTER TABLE emp DROP CONSTRAINT uq_empname;
- Drop Check: ALTER TABLE emp DROP CONSTRAINT chk_salary;
- Drop NOT NULL: ALTER TABLE emp MODIFY sal NULL;
- Drop Default: ALTER TABLE emp MODIFY comm DROP DEFAULT;
4. Modify Constraints
- Add Default: ALTER TABLE emp MODIFY comm DEFAULT 0;
- Change Constraint Name: ALTER TABLE emp RENAME CONSTRAINT old_name TO
new_name;
- Enable/Disable: ALTER TABLE emp ENABLE CONSTRAINT fk_dept;
5. Referential Actions with FK
ALTER TABLE emp ADD CONSTRAINT fk_dept FOREIGN KEY (deptno)
REFERENCES dept(deptno) ON DELETE SET NULL;
ALTER TABLE emp ADD CONSTRAINT fk_dept FOREIGN KEY (deptno)
REFERENCES dept(deptno) ON DELETE CASCADE;
6. Check Constraints in a Table
SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'EMP';
Types: P=Primary Key, R=Foreign Key, U=Unique, C=Check/Not Null
7. Update & Delete Data
Update: UPDATE emp SET emp_name='NewName' WHERE empno=101;
Delete: DELETE FROM emp WHERE empno=101;
Quick Recap Table
Task Syntax
Add PK ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (col);
Drop PK ALTER TABLE t DROP PRIMARY KEY;
Add FK ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES parent(co
Drop FK ALTER TABLE t DROP CONSTRAINT fk;
Add Unique ALTER TABLE t ADD CONSTRAINT uq UNIQUE (col);
Drop Unique ALTER TABLE t DROP CONSTRAINT uq;
Add Check ALTER TABLE t ADD CONSTRAINT chk CHECK (condition);
Drop Check ALTER TABLE t DROP CONSTRAINT chk;
Drop Not Null ALTER TABLE t MODIFY col NULL;
Add Default ALTER TABLE t MODIFY col DEFAULT value;
Drop Default ALTER TABLE t MODIFY col DROP DEFAULT;
Enable Constraint ALTER TABLE t ENABLE CONSTRAINT name;
Disable Constraint ALTER TABLE t DISABLE CONSTRAINT name;
