Advanced Guide to Database Constraints - SQL Office Hours March 2023 scripts

View All Scripts Login to Run Script

Script Name Advanced Guide to Database Constraints - SQL Office Hours March 2023 scripts
Description Scripts for the SQL Ask TOM Office Hours March 2023 session. This discussed advanced uses for foreign keys (FKs) and materialized views (MVs) to apply business rules on your data, including how to Ensure there's always a child row in parent-child relationships Use foreign keys to ensure there's no gaps or overlaps in start/end dates Check the total across child rows equals the value stored on the parent row https://asktom.oracle.com/pls/apex/asktom.search?oh=19984
Area SQL General / DDL
Contributor Chris Saxon (Oracle)
Created Wednesday March 15, 2023

Statement 1

create table jobs ( 
  job_id constraint job_pk primary key,  
  job_title, min_salary, max_salary 
) as 
  select * from hr.jobs

Table created.

Statement 2

create table employees (  
  employee_id primary key, 
  first_name not null, last_name not null,  
  hire_date not null, department_id not null, 
  salary not null, job_id not null 
) as  
  select employee_id, first_name, last_name, hire_date, department_id, salary, job_id 
  from   hr.employees 
  where  department_id is not null

Table created.

Statement 3

create table departments (  
  department_id primary key, department_name not null, manager_id not null 
) as  
  select department_id, department_name, manager_id  
  from   hr.departments 
  where  manager_id is not null

Table created.

Statement 4

create table job_history ( 
  employee_id,  
  start_date not null, end_date, 
  job_id not null, department_id not null, 
  constraint job_history_pk  
    primary key ( employee_id, start_date ), 
  constraint job_history_u 
    unique ( employee_id, end_date ) 
) as 
  select employee_id, start_date,  
         lead ( start_date, 1, end_date ) over (  
            partition by employee_id order by start_date  
         ) end_date, 
         job_id, department_id 
  from   hr.job_history

Table created.

Statement 5

This adds an FK from departments -> employees

Bidirectional FKs

alter table departments  
  add constraint dept_manager_fk  
  foreign key ( manager_id )  
  references employees

Table altered.

Statement 6

This adds a foreign key from employees -> departments

alter table employees  
  add constraint empl_department_fk  
  foreign key ( department_id )  
  references departments

Table altered.

Statement 7

This tries to insert a new employee into a new department. It fails due to the FK from employees -> departments.

insert into employees ( employee_id, first_name, last_name, hire_date, department_id, salary, job_id )  
values ( 42, 'Tess', 'Ting', sysdate, 42, 5000, 'IT_PROG' )

ORA-02291: integrity constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.EMPL_DEPARTMENT_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02291

Statement 8
This tries to insert a new department for a new employee. It also fails due to the FK from departments -> employees! So how do you add a new department & employee at the same time? By making one or both FKs deferrable.
```
insert into departments ( department_id, department_name, manager_id )  
values ( 42, 'Test dept', 42 )
```
```
ORA-02291: integrity constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.DEPT_MANAGER_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721
```
More Details: https://docs.oracle.com/error-help/db/ora-02291
Statement 9
You can't change the deferrable state of a constraint; you must drop & recreate it
```
alter table departments  
  modify constraint dept_manager_fk  
  deferrable
```
```
ORA-00933: SQL command not properly ended 
```
More Details: https://docs.oracle.com/error-help/db/ora-00933

Statement 10

alter table departments 
  drop constraint dept_manager_fk

Table altered.

Statement 11

Making the FK deferrable enables the database to delay checking the constraint until commit.

Deferrable FK

alter table departments  
  add constraint dept_manager_fk  
  foreign key ( manager_id )  
  references employees ( employee_id )  
  deferrable

Table altered.

Statement 12

Although the constraint is deferrable, by default it's still checked at the statement level.

insert into departments ( department_id, department_name, manager_id )  
values ( 42, 'Test dept', 42 )

ORA-02291: integrity constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.DEPT_MANAGER_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02291

Statement 13
This enables the database to wait until commit time to check the constraints for deferrable constraints.
Delay constraint validation
```
alter session set constraints = deferred
```
```
Statement processed.
```

Statement 14

The employee -> departments FK is not deferrable, so can't be delayed.

insert into employees ( employee_id, first_name, last_name, hire_date, department_id, salary, job_id )  
values ( 42, 'Tess', 'Ting', sysdate, 42, 5000, 'IT_PROG' )

ORA-02291: integrity constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.EMPL_DEPARTMENT_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02291

Statement 15

The FK on this table is not deferred, so you can insert a new department for a new employee.

insert into departments ( department_id, department_name, manager_id )  
values ( 42, 'Test dept', 42 )

1 row(s) inserted.

Statement 16
The deferred FK constraint is now validated. Only the new department was inserted; the insert for the new employee failed. So the database raises a constraint error.
```
commit
```
```
ORA-02091: transaction rolled back ORA-06512: at "SYS.DBMS_SQL", line 1721
```
More Details: https://docs.oracle.com/error-help/db/ora-02091

Statement 17

insert into departments ( department_id, department_name, manager_id ) 
values ( 42, 'Test dept', 42 )

1 row(s) inserted.

Statement 18

insert into employees ( employee_id, first_name, last_name, hire_date, department_id, salary, job_id ) 
values ( 42, 'Tess', 'Ting', sysdate, 42, 5000, 'IT_PROG' )

1 row(s) inserted.

Statement 19
Switching the order of the inserts and deferring the FK on departments enables you to add the new rows.
```
commit
```
```
Statement processed.
```

Statement 20

create materialized view log on employees 
  with rowid, primary key ( department_id, salary ), 
  sequence 
  including new values

Statement processed.

Statement 21

create materialized view log on departments 
  with rowid, primary key,  
  sequence 
  including new values

Statement processed.

Statement 22

This MV outer joins employees to departments. We'll use this to ensure every department has an employee by checking the employee_id column is not null.

Using MVs to ensure every parent row has a child

create materialized view department_employees_mv  
  refresh fast on commit  
as  
  select e.rowid empl_rid, d.rowid dept_rid,  
         e.employee_id, d.department_id  
  from   employees e, departments d  
  where  d.department_id = e.department_id (+)

ORA-12006: materialized view or zonemap "SQL_FPDCOIUZTVEPDOYPHSLHKCGST"."DEPARTMENT_EMPLOYEES_MV" already exists

More Details: https://docs.oracle.com/error-help/db/ora-12006

Statement 23
This adds the deferrable not null constraint to the MV. If there are any departments with no employees, this will raise an error after the refresh.
```
alter table department_employees_mv   
  modify employee_id   
    constraint deem_employee_nn  
    not null  
    deferrable
```
```
ORA-02264: name already used by an existing constraint 
```
More Details: https://docs.oracle.com/error-help/db/ora-02264
Statement 24
Remove the departments -> employees FK to ensure it's the MV validating all departments have an employee.
```
alter table departments  
  drop constraint dept_manager_fk
```
```
Table altered.
```

Statement 25

insert into departments ( department_id, department_name, manager_id ) 
values ( 99, 'test', 99 )

1 row(s) inserted.

Statement 26

select e.employee_id, d.department_id 
from   employees e, departments d 
where  d.department_id = e.department_id (+) 
and    d.department_id = 99


EMPLOYEE_ID DEPARTMENT_ID
 - 99

Statement 27
The MV is refreshed at this point. The database sees that EMPLOYEE_ID is null for department 99, so raises an exception.
```
commit
```
```
Statement processed.
```

EMPLOYEE_ID	DEPARTMENT_ID
-	99

Statement 28

The transaction was rolled back, removing the new department row.

select e.employee_id, d.department_id  
from   employees e, departments d  
where  d.department_id = e.department_id (+)  
and    d.department_id = 99


EMPLOYEE_ID DEPARTMENT_ID
 - 99

EMPLOYEE_ID	DEPARTMENT_ID
-	99

Statement 29

insert into departments ( department_id, department_name, manager_id ) 
values ( 99, 'test', 99 )

ORA-00001: unique constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.SYS_C00116351545) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 30

insert into employees ( employee_id, first_name, last_name, hire_date, department_id, salary, job_id ) 
values ( 99, 'Tess', 'Ting', sysdate, 99, 5000, 'IT_PROG' )

1 row(s) inserted.

Statement 31
With a new department and employee, the MV refresh succeeds.
```
commit
```
```
Statement processed.
```

Statement 32

select * from department_employees_mv 
where  department_id = 99


EMPL_RID DEPT_RID EMPLOYEE_ID DEPARTMENT_ID
AJ75+kAAkAAAB4VAAB AJ75+mAAkAAAB4lAAB 99 99

EMPL_RID	DEPT_RID	EMPLOYEE_ID	DEPARTMENT_ID
AJ75+kAAkAAAB4VAAB	AJ75+mAAkAAAB4lAAB	99	99

Statement 33

Each job has an associated salary range. Using MVs you can ensure everyone has a salary within their pay band.

Using MVs to check values in one table fall in a range on another table

select * from jobs


JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500

19 rows selected.

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
AD_PRES	President	20080	40000
AD_VP	Administration Vice President	15000	30000
AD_ASST	Administration Assistant	3000	6000
FI_MGR	Finance Manager	8200	16000
FI_ACCOUNT	Accountant	4200	9000
AC_MGR	Accounting Manager	8200	16000
AC_ACCOUNT	Public Accountant	4200	9000
SA_MAN	Sales Manager	10000	20080
SA_REP	Sales Representative	6000	12008
PU_MAN	Purchasing Manager	8000	15000
PU_CLERK	Purchasing Clerk	2500	5500
ST_MAN	Stock Manager	5500	8500
ST_CLERK	Stock Clerk	2008	5000
SH_CLERK	Shipping Clerk	2500	5500
IT_PROG	Programmer	4000	10000
MK_MAN	Marketing Manager	9000	15000
MK_REP	Marketing Representative	4000	9000
HR_REP	Human Resources Representative	4000	9000
PR_REP	Public Relations Representative	4500	10500

Statement 34

create materialized view log on jobs 
  with rowid, primary key ( min_salary, max_salary ), 
  sequence 
  including new values

Statement processed.

Statement 35

create materialized view employee_jobs_mv 
  refresh fast on commit 
as 
  select e.rowid empl_rid, j.rowid job_rid, 
         e.salary, j.min_salary, j.max_salary 
  from   employees e, jobs j 
  where  e.job_id = j.job_id

ORA-12006: materialized view or zonemap "SQL_FPDCOIUZTVEPDOYPHSLHKCGST"."EMPLOYEE_JOBS_MV" already exists

More Details: https://docs.oracle.com/error-help/db/ora-12006

Statement 36

This checks that each employees salary falls within the min and max allowed for their job.

alter table employee_jobs_mv  
  add constraint emjo_salary_c  
  check ( salary between min_salary and max_salary ) 
  deferrable

ORA-02264: name already used by an existing constraint

More Details: https://docs.oracle.com/error-help/db/ora-02264

Statement 37
Increase all employees salary 10x. This means every one is earning more than the maximum allowed for their role!
```
update employees   
set    salary = salary * 10
```
```
108 row(s) updated.
```
Statement 38
The database tries to validate the MVs constraint ensuring employees are paid within their band. This fails and the transaction rolls back.
```
commit
```
```
Statement processed.
```
Statement 39
Add a column to store the total salaries for each department.
```
alter table departments   
  add ( total_salary number )
```
```
Table altered.
```

Statement 40

Set this new column

update departments d  
set    total_salary = (   
  select sum ( salary ) from employees e  
  where  d.department_id = e.department_id  
)

13 row(s) updated.

Statement 41
```
commit
```
```
Statement processed.
```

Statement 42

drop materialized view log on departments

Statement processed.

Statement 43

create materialized view log on departments 
  with rowid, primary key ( total_salary ),  
  sequence 
  including new values

Statement processed.

Statement 44

exec dbms_mview.refresh ( 'DEPARTMENT_EMPLOYEES_MV', 'C' )

Statement processed.

Statement 45

This MV gets the total of employee salaries per department

create materialized view department_salaries_mv  
refresh fast on commit as  
  select d.department_id, d.total_salary,  
         sum ( e.salary ) emp_salaries,   
         count(*)  
  from   employees e, departments d  
  where  d.department_id = e.department_id  
  group  by d.department_id, d.total_salary

ORA-12006: materialized view or zonemap "SQL_FPDCOIUZTVEPDOYPHSLHKCGST"."DEPARTMENT_SALARIES_MV" already exists

More Details: https://docs.oracle.com/error-help/db/ora-12006

Statement 46

This checks that the summary column on departments matches the total value of employee salaries in the table

alter table department_salaries_mv   
  add constraint desa_total_salaries_c   
  check ( total_salary = emp_salaries )  
  deferrable

ORA-02264: name already used by an existing constraint

More Details: https://docs.oracle.com/error-help/db/ora-02264

Statement 47

This only updates the employee salaries; not the department totals...

update employees  
set    salary = salary * 1.1  
where  job_id = 'IT_PROG'

7 row(s) updated.

Statement 48
...so constraint validation fails after the MV refreshes.
```
commit
```
```
Statement processed.
```

Statement 49

update employees 
set    salary = salary * 1.1 
where  job_id = 'IT_PROG'

7 row(s) updated.

Statement 50

This updates the department totals after giving the pay rises

update departments d  
set    total_salary = (   
  select sum ( salary ) from employees e  
  where  d.department_id = e.department_id  
)

13 row(s) updated.

Statement 51
The MV constraint is valid so you can commit the transaction.
```
commit
```
```
Statement processed.
```

Statement 52

Enforce consecutive start/end dates with FKs

select * from job_history  
order  by employee_id, start_date


EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID
101 21-SEP-97 28-OCT-01 AC_ACCOUNT 110
101 28-OCT-01 15-MAR-05 AC_MGR 110
102 13-JAN-01 24-JUL-06 IT_PROG 60
114 24-MAR-06 31-DEC-07 ST_CLERK 50
120 18-JUL-04 16-MAR-19 ST_MAN 50
122 01-JAN-07 31-DEC-07 ST_CLERK 50
176 24-MAR-06 01-JAN-07 SA_REP 80
176 01-JAN-07 31-DEC-07 SA_MAN 80
200 17-SEP-95 01-JUL-02 AD_ASST 90
200 01-JUL-02 31-DEC-06 AC_ACCOUNT 90
201 17-FEB-04 19-DEC-07 MK_REP 20

11 rows selected.

Statement 53
This FK means an employees start_date must have a matching end_date row. This ensures that employees have no gaps or overlaps in their employment history. Note NOVALIDATE is necessary; when a new employee joins there is no previous end date for their start date to point to!
```
alter table job_history   
  add constraint johi_start_end_fk  
  foreign key ( employee_id, start_date )  
  references job_history ( employee_id, end_date )  
  novalidate
```
```
Table altered.
```

EMPLOYEE_ID	START_DATE	END_DATE	JOB_ID	DEPARTMENT_ID
101	21-SEP-97	28-OCT-01	AC_ACCOUNT	110
101	28-OCT-01	15-MAR-05	AC_MGR	110
102	13-JAN-01	24-JUL-06	IT_PROG	60
114	24-MAR-06	31-DEC-07	ST_CLERK	50
120	18-JUL-04	16-MAR-19	ST_MAN	50
122	01-JAN-07	31-DEC-07	ST_CLERK	50
176	24-MAR-06	01-JAN-07	SA_REP	80
176	01-JAN-07	31-DEC-07	SA_MAN	80
200	17-SEP-95	01-JUL-02	AD_ASST	90
200	01-JUL-02	31-DEC-06	AC_ACCOUNT	90
201	17-FEB-04	19-DEC-07	MK_REP	20

Statement 54

select * from job_history 
where  employee_id = 101 
order  by start_date


EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID
101 21-SEP-97 28-OCT-01 AC_ACCOUNT 110
101 28-OCT-01 15-MAR-05 AC_MGR 110

2 rows selected.

EMPLOYEE_ID	START_DATE	END_DATE	JOB_ID	DEPARTMENT_ID
101	21-SEP-97	28-OCT-01	AC_ACCOUNT	110
101	28-OCT-01	15-MAR-05	AC_MGR	110

Statement 55

Employee 101 has no job that ends on 1 Jan 2023, so you can't insert this row

insert into job_history ( employee_id, start_date, job_id, department_id )  
values ( 101, date'2023-01-01', 'IT_PROG', 20 )

ORA-02291: integrity constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.JOHI_START_END_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02291

Statement 56

This points to the previous end_date for the employee, so you can insert it.

insert into job_history ( employee_id, start_date, job_id, department_id )  
values ( 101, date'2005-03-15', 'IT_PROG', 20 )

1 row(s) inserted.

Statement 57

The start-end date constraint means you can't add job histories for new employees!

insert into job_history ( employee_id, start_date, job_id, department_id )  
values ( 999, date'2023-01-01', 'IT_PROG', 20 )

ORA-02291: integrity constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.JOHI_START_END_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02291

Statement 58

To add a new employee's history, you must first disable the constraint...

alter table job_history  
  modify constraint johi_start_end_fk  
  disable

Table altered.

Statement 59

...then insert the new row...

insert into job_history ( employee_id, start_date, job_id, department_id )  
values ( 999, date'2023-01-01', 'IT_PROG', 20 )

1 row(s) inserted.

Statement 60

...and re-enable the constraint without validating it!

alter table job_history  
  modify constraint johi_start_end_fk  
  enable  
  novalidate

Table altered.

Statement 61

End the employee's current job...

update job_history   
set    end_date = date'2023-03-21'  
where  employee_id = 999  
and    end_date is null

1 row(s) updated.

Statement 62

...and add a new one

insert into job_history ( employee_id, start_date, job_id, department_id )  
values ( 999, date'2023-03-21', 'PROGRAMMER', 20 )

1 row(s) inserted.

Statement 63
```
commit
```
```
Statement processed.
```
Statement 64
Jobs may have attributes which are specific to a role. For example, only salespeople earn commission. The employees table will have many child tables for this data. Each employee row should only have an entry in the one child table that applies to their role. This adds a "redundant" unique constraint that includes the primary key of the table. The child tables will point to this to ensure the only store rows of the correct type.
Super/subtype relationships
```
alter table employees   
  add constraint empl_employee_job_u  
  unique ( job_id, employee_id )
```
```
Table altered.
```

Statement 65

The check constraint on JOB_ID ensures you only insert developer jobs in this table. The FK on ( job_id, employee_id ) ensures that the parent row is the same job type.

create table developers (  
  employee_id   
    constraint deve_employee_fk  
    references employees  
    constraint developer_pk  
    primary key,  
  job_id  
    -- Ensure only developers can be inserted  
    constraint deve_job_c  
    check ( job_id = 'IT_PROG' )  
    not null,  
  primary_programming_language varchar2(30)  
    not null,  
  -- Ensure only developers can be inserted  
  constraint deve_employee_job_fk  
    foreign key ( job_id, employee_id )  
    references employees ( job_id, employee_id )  
)

Table created.

Statement 66

create table sales_staff (  
  employee_id   
    constraint sast_employee_fk  
    references employees  
    constraint sales_staff_pk  
    primary key,  
  job_id  
    -- Ensure only sales staff can be inserted  
    constraint sast_job_c  
    check ( job_id in ( 'SA_MAN', 'SA_REP' ) )  
    not null,  
  commission_pct number  
    not null,  
  -- Ensure only sales staff can be inserted  
  constraint sast_employee_job_fk  
    foreign key ( job_id, employee_id )  
    references employees ( job_id, employee_id )  
)

Table created.

Statement 67

You can't insert developer jobs into the sales staff table.

insert into sales_staff   
values ( 42, 'IT_PROG', 0.1 )

ORA-02290: check constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.SAST_JOB_C) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02290

Statement 68

Parent rows for sales people must have a sales job

insert into sales_staff   
values ( 42, 'SA_MAN', 0.1 )

ORA-02291: integrity constraint (SQL_FPDCOIUZTVEPDOYPHSLHKCGST.SAST_EMPLOYEE_JOB_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02291

Statement 69
To add a developer row, the job must be a programming role and the parent row have the same job type.
```
insert into developers   
values ( 42, 'IT_PROG', 'SQL' )
```
```
1 row(s) inserted.
```
Statement 70
```
commit
```
```
Statement processed.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database