Learning Objectives – after this chapter you will be able to:
- Explain integrity constraints and the difference between standard rules and business rules.
- Distinguish column constraints from table constraints.
- Define NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY (with ON DELETE CASCADE) and CHECK constraints.
- Describe the six sample tables used throughout the course and their relationships.
- Query the data dictionary (
TAB,USER_CONSTRAINTS) for table and constraint information.
What is a Constraint?
A constraint is a rule declared on a table to maintain data integrity – hence the name integrity constraint. Once created, the Oracle server checks it whenever a row is inserted, updated or deleted, and the operation fails if the constraint is not satisfied.
- Standard rules – universal rules for primary and foreign keys: every table has a primary key that is unique and not null; a foreign key derives its values from the parent key.
- Business rules – rules of one application, e.g. an employee’s salary must not be less than 2000, or a bank account balance must be at least 500.
Constraints are normally defined in CREATE TABLE but can be added later with ALTER TABLE. They are stored in the data dictionary. Each constraint has a name – given with the CONSTRAINT option or generated by the system as SYS_Cn. Naming constraints yourself (convention used: TABLENAME_COLUMN_TYPE) makes them easier to refer to later.
Column Constraints vs Table Constraints
| Column constraint | Table constraint | |
|---|---|---|
| Where written | Immediately after the column definition | After the column definitions, as a separate item |
| Columns | Only the column on which it is defined | May refer to more than one column (column names are listed) |
| Typical use | Single-column primary key | Composite primary key, composite unique / foreign key, multi-column CHECK |
Any column-level constraint can also be written at table level, but a constraint involving more than one column must be a table constraint. NOT NULL is written at column level.
Types of Integrity Constraints
| Constraint | Rule and behaviour |
|---|---|
| NOT NULL | Prevents null values. Automatically applied to PRIMARY KEY columns. |
| PRIMARY KEY | Uniquely identifies rows. Only one per table; may contain several columns (composite – must be a table constraint). Oracle creates a unique index and applies NOT NULL. |
| UNIQUE | No duplicate values in the column(s). Oracle automatically creates a unique index. Multi-column uniqueness must be a table constraint. |
| FOREIGN KEY (REFERENCES) | Provides referential integrity. A parent row cannot be deleted while child rows exist – unless ON DELETE CASCADE is given, in which case the child rows are deleted with the parent. If the referenced column has the same name, it can be omitted (REFERENCES courses). |
| CHECK | Condition that must be true before insert/update. It may not contain a reference to SYSDATE or a subquery. At column level it refers only to that column; at table level it may refer to several columns of the same table – never to other tables. |
Column and table constraints together (based on the chapter script)
create table course_faculty
( fcode varchar2(5) constraint course_faculty_fcode_fk references faculty(fcode),
ccode varchar2(5) constraint course_faculty_ccode_fk references courses(ccode)
on delete cascade,
grade char(1) constraint course_faculty_grade_chk
check ( upper(grade) in ('A','B','C') ),
constraint course_faculty_pk primary key (ccode, fcode) -- table constraint
); Character comparison is case sensitive, so the CHECK uses
UPPER(grade) to accept both ‘a’ and ‘A’. A table-level CHECK such as CHECK (stdate <= enddate) compares two columns.The Six Sample Tables
The course uses the data of a typical training institute. Understanding these tables and their relationships is essential for all later chapters.
| Table | Stores | Key columns and constraints |
|---|---|---|
| COURSES | Courses offered | PK CCODE; NAME unique; DURATION and FEE checked (CCODE, NAME, DURATION, FEE, PREREQUISITE) |
| FACULTY | Faculty members | PK faculty code (FCODE in the script); NAME, QUAL, EXP |
| COURSE_FACULTY | Which faculty can take which course, with GRADE A (very good), B (good), C (average) | PK (CCODE, FCODE); FKs to COURSES and FACULTY; GRADE in A/B/C |
| BATCHES | Completed, running and scheduled batches | PK BCODE; FKs CCODE, FCODE; STDATE not null; ENDDATE null if running; TIMING 1 (morning), 2 (afternoon), 3 (evening); STDATE <= ENDDATE |
| STUDENTS | Students (a new roll number for every course joined) | PK ROLLNO; FK BCODE; GENDER M/F; NAME, DJ (date of joining), PHONE, EMAIL |
| PAYMENTS | Fee payments (a student may pay in instalments) | PK (ROLLNO, DP); FK ROLLNO; AMOUNT checked |
COURSESccode (PK)
BATCHESbcode (PK)
ccode, fcode (FK)
ccode, fcode (FK)
STUDENTSrollno (PK)
bcode (FK)
bcode (FK)
PAYMENTS(rollno, dp) PK
rollno (FK)
rollno (FK)
FACULTY is the parent of BATCHES and COURSE_FACULTY; COURSES is the parent of BATCHES and COURSE_FACULTY. The tables are created with one script and filled with sample data by another script run with the SQL*Plus START command (both scripts are listed in the PDF). Note: the column descriptions in the PDF call the faculty code FACCODE and give slightly different CHECK values than the script; the script is what actually creates the tables.
Getting Information from the Data Dictionary
The data dictionary keeps information about tables, constraints, procedures etc. Oracle provides data dictionary views (relational views, not tables) to read it.
Data dictionary queries
select * from tab; -- tables in your schema select * from user_constraints; -- all constraints of your tables select constraint_name from user_constraints where table_name = 'BATCHES'; -- table name in UPPERCASE
Output
CONSTRAINT_NAME ------------------------------ BATCHES_STDATE_NN BATCHES_TIMING_CHK BATCHES_DATES_CHK BATCHES_PK BATCHES_CCODE_FK BACHES_FCODE_FK
CONSTRAINT_TYPE values: C – check (NOT NULL is also a check), U – unique, R – reference (foreign key), P – primary key, V – check on view. Object names are stored in uppercase in the data dictionary.
Key Takeaways
- Constraints enforce standard and business rules automatically on every insert, update and delete.
- Composite keys and multi-column rules need table constraints.
- A parent row with children cannot be deleted unless ON DELETE CASCADE is specified.
- CHECK cannot use SYSDATE, subqueries or other tables.
Chapter 4 Notes (PDF)
ch4.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 4 notes (PDF). Click a question to see the answer.
Answer : An integrity constraint is a rule defined on a table to maintain the integrity of data. Once created, the Oracle server makes sure it is not violated whenever a row is inserted, deleted or updated; if the constraint is not satisfied the operation fails.
Answer : Standard rules are universal rules related to primary and foreign keys – every table must have a primary key that is unique and not null, and a foreign key must derive its values from the parent key. Business rules relate to a single application, e.g. rejecting an employee row whose salary is less than 2000, or requiring a bank account balance of at least 500.
Answer : A constraint is named with the CONSTRAINT option; otherwise the system assigns a name of the form SYS_Cn (n is a number). Giving your own name (e.g. TABLENAME_COLUMN_TYPE such as courses_name_nn) is recommended so that referring to the constraint later is easier.
Answer : A column constraint is written immediately after a column and defines a rule only for that column. A table constraint is written at table level, must name the column(s) it applies to and may refer to more than one column. A constraint that deals with more than one column (e.g. a composite primary key) must be given at table level.
Answer : It creates a unique index to enforce uniqueness and defines a NOT NULL constraint to prevent null values. A table can have only one primary key, which may consist of more than one column.
Answer : A composite primary key must be defined as a table constraint, e.g. CONSTRAINT course_faculty_pk PRIMARY KEY (ccode, fcode) in the COURSE_FACULTY table.
Answer : UNIQUE enforces uniqueness of the values in the given column(s), and Oracle automatically creates a unique index for it. If two or more columns together must be unique, the UNIQUE constraint must be given at table level.
Answer : Once a foreign key is defined, Oracle does not allow a parent row to be deleted if dependent child rows exist – e.g. a course cannot be deleted from COURSES while rows for it exist in COURSE_FACULTY.
Answer : ON DELETE CASCADE, given with the REFERENCES clause of a foreign key, makes Oracle delete all child rows automatically when the parent row is deleted.
Answer : When the referenced column of the parent table has the same name as the foreign key column, e.g. REFERENCES courses is enough instead of REFERENCES courses(ccode).
Answer : The condition may not contain a reference to the pseudo column SYSDATE or a subquery. As a column constraint it can refer only to its own column; as a table constraint it can refer to several columns of the same table; in neither case can it refer to columns of other tables.
Answer : Character comparison is case sensitive, so CHECK (grade IN ('A','B','C')) would reject lowercase letters. Using CHECK (UPPER(grade) IN ('A','B','C')) converts the grade to uppercase before comparison and ignores case differences.
Answer : BATCHES stores completed, running and scheduled batches with columns BCODE, CCODE, FCODE, STDATE, ENDDATE and TIMING. BCODE is the primary key; CCODE references COURSES and FCODE references FACULTY; STDATE is not null; ENDDATE is null for batches not yet completed; TIMING must be 1 (morning), 2 (afternoon) or 3 (evening); and STDATE must be less than or equal to ENDDATE.
Answer : COURSES is the parent and COURSE_FACULTY is the child – a one-to-many relationship. CCODE of COURSE_FACULTY is a foreign key referencing CCODE of COURSES, since one course can be handled by many faculty members.
Answer : SELECT * FROM tab lists the tables of your schema and SELECT * FROM user_constraints lists all constraints of your tables. To see constraints of one table, filter on TABLE_NAME in uppercase, e.g. WHERE table_name = 'BATCHES'. CONSTRAINT_TYPE shows C (check), U (unique), R (reference), P (primary key) or V (check on view); for example, WHERE constraint_type = 'C' lists CHECK constraints.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
- Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
- Oracle Corporation. (n.d.). Oracle Database Reference (initialization parameters and data dictionary views), 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/
- Date, C. J. (2003). An Introduction to Database Systems (8th ed.). Addison-Wesley.
- Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.