Oracle Live SQL - Script: A beginner's guide to database constraints

Statement 1

This defines STUDENT_ID as the primary key and EMAIL_ADDRESS as a unique constraint. This means you can't store duplicate values in these columns

Inline PRIMARY KEY & UNIQUE CONSTRAINTS

create table students (  
  student_id    integer   
    constraint student_pk   
    primary key,  
  email_address varchar2(320)  
    constraint stud_email_u   
    unique,  
  registration_number varchar2(100)  
)

Table created.

Statement 2

There is already a primary key on the table, so this statement fails

A table can only have one primary key

alter table students   
  add primary key ( registration_number )

ORA-02260: table can have only one primary key

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

Statement 3

A table can have many unique constraints

alter table students   
  add unique ( registration_number )

Table altered.

Statement 4

Note that the unnamed constraint on REGISTRATION_NUMBER gets a system-generated name

View the constraints

select constraint_name, constraint_type   
from   user_constraints  
where  table_name = 'STUDENTS'


CONSTRAINT_NAME CONSTRAINT_TYPE
STUDENT_PK P
STUD_EMAIL_U U
SYS_C00114593131 U

3 rows selected.

CONSTRAINT_NAME	CONSTRAINT_TYPE
STUDENT_PK	P
STUD_EMAIL_U	U
SYS_C00114593131	U

Statement 5

insert into students values ( 1, 'first.id@test.com', 'ABC123' )

1 row(s) inserted.

Statement 6

select * from students


STUDENT_ID EMAIL_ADDRESS REGISTRATION_NUMBER
1 first.id@test.com ABC123

STUDENT_ID	EMAIL_ADDRESS	REGISTRATION_NUMBER
1	first.id@test.com	ABC123

Statement 7

insert into students values ( 1, 'duplicate.id@test.com', 'DEF456' )

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

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

Statement 8

insert into students values ( 2, 'first.id@test.com', 'GHI879' )

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

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

Statement 9

insert into students values ( 3, 'duplicate.reg@test.com', 'ABC123' )

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

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

Statement 10

insert into students values ( null, 'null.id@test.com', 'JKL123' )

ORA-01400: cannot insert NULL into ("SQL_LBZXMQQATQMTXRTDILZWERGGD"."STUDENTS"."STUDENT_ID") ORA-06512: at "SYS.DBMS_SQL", line 1721

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

Statement 11

insert into students values ( 4, null, null )

1 row(s) inserted.

Statement 12

insert into students values ( 5, null, null )

1 row(s) inserted.

Statement 13

Note that Oracle Database does not consider nulls to be equal for unique constraints. So many rows can store null in the columns of unique constraints

No duplicates inserted

select * from students


STUDENT_ID EMAIL_ADDRESS REGISTRATION_NUMBER
1 first.id@test.com ABC123
4  -  - 
5  -  - 

3 rows selected.

STUDENT_ID	EMAIL_ADDRESS	REGISTRATION_NUMBER
1	first.id@test.com	ABC123
4	-	-
5	-	-

Statement 14

You can insert values for the same letters in different case by default

Unique constraints are not case-sensitive

insert into students values ( 6, 'FIRST.ID@TEST.COM', 'abc123' )

1 row(s) inserted.

Statement 15

The COLLATE clause enables case-insensitive comparison. This reveals that there are two rows with the same letters for EMAIL_ADDRESS & REGISTRATION_NUMBER, but in different cases

with rws as (  
  select   
    /* Enable case-insenstive comparison */  
    email_address collate binary_ci email_ci,  
    registration_number collate binary_ci reg_ci  
  from   students  
)  
  select email_ci, reg_ci, count(*)   
  from   rws  
  group  by email_ci, reg_ci  
  having count(*) > 1


EMAIL_CI REG_CI COUNT(*)
first.id@test.com ABC123 2
 -  - 2

2 rows selected.

EMAIL_CI	REG_CI	COUNT(*)
first.id@test.com	ABC123	2
-	-	2

Statement 16

delete students 
where  student_id = 6

1 row(s) deleted.

Statement 17

You don't need to name unique constraints to drop them; just list their columns

Remove unique constraint

alter table students   
  drop unique ( email_address )

Table altered.

Statement 18

You can't create function-based unique constraints (or primary keys)

alter table students   
  add unique ( email_address collate binary_ci )

ORA-00904: : invalid identifier

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

Statement 19

This creates a function-based unique index to enable case-insensitive uniqueness of EMAIL_ADDRESS

Case-insensitive uniqueness

create unique index stud_email_u  
  on students ( email_address collate binary_ci )

Index created.

Statement 20

Notice that the PK and UC also have unique indexes. Oracle Database creates these automatically to police the constraint

Unique indexes

select index_name, index_type, uniqueness, 
       column_name, data_default 
from   user_indexes 
join   user_ind_columns using ( table_name, index_name ) 
join   user_tab_cols using ( table_name, column_name ) 
where  table_name = 'STUDENTS'


INDEX_NAME INDEX_TYPE UNIQUENESS COLUMN_NAME DATA_DEFAULT
STUDENT_PK NORMAL UNIQUE STUDENT_ID  - 
STUD_EMAIL_U FUNCTION-BASED NORMAL UNIQUE SYS_NC00004$ NLSSORT("EMAIL_ADDRESS" COLLATE "BINARY_CI",'nls_sort=''BINARY_CI''')
SYS_C00114593131 NORMAL UNIQUE REGISTRATION_NUMBER  - 

3 rows selected.

INDEX_NAME	INDEX_TYPE	UNIQUENESS	COLUMN_NAME	DATA_DEFAULT
STUDENT_PK	NORMAL	UNIQUE	STUDENT_ID	-
STUD_EMAIL_U	FUNCTION-BASED NORMAL	UNIQUE	SYS_NC00004$	NLSSORT("EMAIL_ADDRESS" COLLATE "BINARY_CI",'nls_sort=''BINARY_CI''')
SYS_C00114593131	NORMAL	UNIQUE	REGISTRATION_NUMBER	-

Statement 21

insert into students values ( 6, 'FIRST.ID@TEST.COM', 'ZZZ999' )

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

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

Statement 22

drop index stud_email_u

Index dropped.

Statement 23

From 12.2, you can set a column to use case-insensitive comparisons by default

Define column as case-insenstive

alter table students  
  modify email_address collate binary_ci

Table altered.

Statement 24

alter table students  
  add constraint stud_email_u 
  unique ( email_address )

Table altered.

Statement 25

insert into students values ( 6, 'FIRST.ID@TEST.COM', 'abc123' )

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

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

Statement 26

When you add a unique constraint on a case-insensitive column, the database places the index on a virtual column on the NLS expression. This is essentially the same as the unique function-based index.

Indexes

select index_name, index_type, uniqueness,   
       column_name, data_default   
from   user_indexes   
join   user_ind_columns using ( table_name, index_name )   
join   user_tab_cols using ( table_name, column_name )   
where  table_name = 'STUDENTS'


INDEX_NAME INDEX_TYPE UNIQUENESS COLUMN_NAME DATA_DEFAULT
STUDENT_PK NORMAL UNIQUE STUDENT_ID  - 
STUD_EMAIL_U FUNCTION-BASED NORMAL UNIQUE SYS_NC00004$ NLSSORT("EMAIL_ADDRESS",'nls_sort=''BINARY_CI''')
SYS_C00114593131 NORMAL UNIQUE REGISTRATION_NUMBER  - 

3 rows selected.

INDEX_NAME	INDEX_TYPE	UNIQUENESS	COLUMN_NAME	DATA_DEFAULT
STUDENT_PK	NORMAL	UNIQUE	STUDENT_ID	-
STUD_EMAIL_U	FUNCTION-BASED NORMAL	UNIQUE	SYS_NC00004$	NLSSORT("EMAIL_ADDRESS",'nls_sort=''BINARY_CI''')
SYS_C00114593131	NORMAL	UNIQUE	REGISTRATION_NUMBER	-

Statement 27

Dropping unique constraints/primary keys also removes the supporting index

Remove unique constraint

alter table students  
  drop unique ( registration_number )

Table altered.

Statement 28

Using the KEEP INDEX clause, you instruct the database to leave the index in place when removing PK & UCs

Preserve the index when removing constraints

alter table students  
  drop primary key  
  keep index

Table altered.

Statement 29

Check the indexes

select index_name, index_type,   
       column_name, data_default  
from   user_indexes  
join   user_ind_columns using ( table_name, index_name )  
join   user_tab_cols using ( table_name, column_name )  
where  table_name = 'STUDENTS'


INDEX_NAME INDEX_TYPE COLUMN_NAME DATA_DEFAULT
STUDENT_PK NORMAL STUDENT_ID  - 
STUD_EMAIL_U FUNCTION-BASED NORMAL SYS_NC00004$ NLSSORT("EMAIL_ADDRESS",'nls_sort=''BINARY_CI''')

2 rows selected.

INDEX_NAME	INDEX_TYPE	COLUMN_NAME	DATA_DEFAULT
STUDENT_PK	NORMAL	STUDENT_ID	-
STUD_EMAIL_U	FUNCTION-BASED NORMAL	SYS_NC00004$	NLSSORT("EMAIL_ADDRESS",'nls_sort=''BINARY_CI''')

Statement 30

The USING INDEX clause enables you to specify which index you want the database to use for the constraint

USING INDEX clause

alter table students  
  add constraint student_pk  
  primary key ( student_id )  
  using index student_pk

Table altered.

Statement 31

create table courses ( 
  course_id   integer  
    constraint course_pk primary key, 
  course_code varchar2(30) 
    constraint cour_code_u unique 
)

Table created.

Statement 32

begin 
  insert into courses values ( 1, 'SQL101' ); 
  insert into courses values ( 2, 'PLSQL101' ); 
end;

Statement processed.

Statement 33

By default, foreign keys point to the parent table's primary key and inherit the data type of the PKs columns. Also note the multi-column PK on this table.

Foreign keys

create table student_courses (   
  student_id    
    constraint stco_student_fk   
    references students,   
  course_id     
    constraint stco_course_fk   
    references courses,   
  constraint student_course_pk   
    primary key ( student_id, course_id )   
)

Table created.

Statement 34

select column_name, data_type, data_length, data_precision, data_scale 
from   user_tab_columns 
where  table_name = 'STUDENT_COURSES'


COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
STUDENT_ID NUMBER 22  - 0
COURSE_ID NUMBER 22  - 0

2 rows selected.

COLUMN_NAME	DATA_TYPE	DATA_LENGTH	DATA_PRECISION	DATA_SCALE
STUDENT_ID	NUMBER	22	-	0
COURSE_ID	NUMBER	22	-	0

Statement 35

insert into student_courses values ( 1, 1 )

1 row(s) inserted.

Statement 36

insert into student_courses values ( 2, 99 )

ORA-02291: integrity constraint (SQL_LBZXMQQATQMTXRTDILZWERGGD.STCO_COURSE_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 37

insert into student_courses values ( 42, 2 )

ORA-02291: integrity constraint (SQL_LBZXMQQATQMTXRTDILZWERGGD.STCO_STUDENT_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 38

With the foreign key in place, the STUDENT_ID must match a value in STUDENTS.STUDENT_ID. COURSE_ID must have a matching value in COURSES.COURSE_ID

select * from student_courses


STUDENT_ID COURSE_ID
1 1

STUDENT_ID	COURSE_ID
1	1

Statement 39

drop table student_courses 
  cascade constraints purge

Table dropped.

Statement 40

You can tell the database which columns from the parent table FKs point to. These can be either primary keys or unique constraints.

Foreign keys can reference unique constraints

create table student_courses (  
  email_address   
    constraint stco_student_fk  
    references students ( email_address ),  
  course_code  
    constraint stco_course_fk  
    references courses ( course_code ),  
  constraint student_course_pk  
    primary key ( email_address, course_code )  
)

Table created.

Statement 41

The foreign key columns have inherited the VARCHAR2 type from the parent unique constraints.

select column_name, data_type, data_length, data_precision, data_scale  
from   user_tab_columns  
where  table_name = 'STUDENT_COURSES'


COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
EMAIL_ADDRESS VARCHAR2 320  -  - 
COURSE_CODE VARCHAR2 30  -  - 

2 rows selected.

COLUMN_NAME	DATA_TYPE	DATA_LENGTH	DATA_PRECISION	DATA_SCALE
EMAIL_ADDRESS	VARCHAR2	320	-	-
COURSE_CODE	VARCHAR2	30	-	-

Statement 42

insert into student_courses values ( 'first.id@test.com', 'SQL101' )

1 row(s) inserted.

Statement 43

insert into student_courses values ( 'first.id@test.com', 'ZZZZZZ' )

ORA-02291: integrity constraint (SQL_LBZXMQQATQMTXRTDILZWERGGD.STCO_COURSE_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 44

insert into student_courses values ( 'missing@test.com', 'SQL101' )

ORA-02291: integrity constraint (SQL_LBZXMQQATQMTXRTDILZWERGGD.STCO_STUDENT_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 45

select * from student_courses


EMAIL_ADDRESS COURSE_CODE
first.id@test.com SQL101

EMAIL_ADDRESS	COURSE_CODE
first.id@test.com	SQL101

Statement 46

Oracle Database does not support cascading updates of PK/UC columns to child tables. It's good practice to point FKs and PKs to avoid this problem.

Cascading updates

update students  
set    email_address = 'new@test.com'  
where  email_address = 'first.id@test.com'

ORA-02292: integrity constraint (SQL_LBZXMQQATQMTXRTDILZWERGGD.STCO_STUDENT_FK) violated - child record found ORA-06512: at "SYS.DBMS_SQL", line 1721

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

Statement 47

Reset table so FKs point to PKs

drop table student_courses  
  cascade constraints purge

Table dropped.

Statement 48

create table student_courses ( 
  student_id  
    constraint stco_student_fk 
    references students, 
  course_id   
    constraint stco_course_fk 
    references courses, 
  constraint student_course_pk 
    primary key ( student_id, course_id ) 
)

Table created.

Statement 49

insert into student_courses values ( 1, 1 )

1 row(s) inserted.

Statement 50

update students 
set    email_address = 'new@test.com' 
where  email_address = 'first.id@test.com'

1 row(s) updated.

Statement 51

FKs can have many columns. To do this the data types of the child columns must match those in the parent constraint's columns. You can omit the data type when defining the child columns to ensure this happens.

Multi-column foreign keys

create table student_course_results (  
  student_course_result_id integer  
    constraint student_course_result_pk  
    primary key,  
  student_id ,  
  course_id  ,  
  exam_id    integer,  
  percent_correct number,  
  constraint stcr_student_course_fk  
    foreign key ( student_id, course_id )  
    references student_courses,  
  unique ( student_id, course_id, exam_id )  
)

Table created.

Statement 52

If you insert NULL into any of the columns of a composite FK, the database DOES NOT validate its other columns have a matching value in the parent constraint. This means you can insert orphaned values.

Beware optional multicolumn FKs!

insert into student_course_results values ( 1, 99, null, 1, 0 )

1 row(s) inserted.

Statement 53

There is no STUDENT_ID = 99 in the parent table. Because COURSE_ID is null in the child, the database allows it

Orphaned values in optional multicolumn FKs

select * from student_course_results stcr   
where  not exists (   
  select * from student_courses stco   
  where  stco.student_id = stcr.student_id   
)


STUDENT_COURSE_RESULT_ID STUDENT_ID COURSE_ID EXAM_ID PERCENT_CORRECT
1 99  - 1 0

STUDENT_COURSE_RESULT_ID	STUDENT_ID	COURSE_ID	EXAM_ID	PERCENT_CORRECT
1	99	-	1	0

Statement 54

truncate table student_course_results

Table truncated.

Statement 55

You can make a column mandatory using CHECK constraints

Not null check constraints

alter table student_course_results  
  add constraint stcr_course_nn   
  check ( course_id is not null )

Table altered.

Statement 56

You can't use ADD CONSTRAINT to define a not null column; you must modify the column itself

alter table student_course_results  
  add constraint stcr_student_nn  
  student_id integer not null

ORA-01430: column being added already exists in table

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

Statement 57

Like this

NOT NULL constraints

alter table student_course_results   
  modify (   
    student_id constraint stcr_student_nn not null   
  )

Table altered.

Statement 58

CHECK IS NOT NULL and NOT NULL constraints look similar at first glance. Both have type C and the same condition in the data dictionary

select constraint_name, constraint_type, search_condition   
from   user_constraints  
where  table_name = 'STUDENT_COURSE_RESULTS'  
and    constraint_type = 'C'


CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
STCR_COURSE_NN C  course_id is not null 
STCR_STUDENT_NN C "STUDENT_ID" IS NOT NULL

2 rows selected.

CONSTRAINT_NAME	CONSTRAINT_TYPE	SEARCH_CONDITION
STCR_COURSE_NN	C	course_id is not null
STCR_STUDENT_NN	C	"STUDENT_ID" IS NOT NULL

Statement 59

Check constraints always raise ORA-02290

insert into student_course_results values ( 1, 99, null, 1, 0 )

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

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

Statement 60

NOT NULL constraints have a specific error: ORA-01400. This makes it easier to identify missing data errors compared to CHECK constraints

insert into student_course_results values ( 2, null, 99, 1, 0 )

ORA-01400: cannot insert NULL into ("SQL_LBZXMQQATQMTXRTDILZWERGGD"."STUDENT_COURSE_RESULTS"."STUDENT_ID") ORA-06512: at "SYS.DBMS_SQL", line 1721

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

Statement 61

Only NOT NULL constraints show up in the NULLABLE column of the column views.

select column_name, nullable   
from   user_tab_columns  
where  table_name = 'STUDENT_COURSE_RESULTS'  
and    column_name in ( 'STUDENT_ID', 'COURSE_ID' )


COLUMN_NAME NULLABLE
STUDENT_ID N
COURSE_ID Y

2 rows selected.

COLUMN_NAME	NULLABLE
STUDENT_ID	N
COURSE_ID	Y

Statement 62

select * from student_course_results 
where  student_id is null

no data found

Statement 63

The optimizer knows STUDENT_ID cannot store NULL. So this is an impossible condition. It adds the FILTER operation above the table scan, stopping this from running.

NOT NULL optimization

select *    
from   dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'TYPICAL LAST +PREDICATE')


PLAN_TABLE_OUTPUT
SQL_ID  14u9w893qdv0f, child number 0
-------------------------------------
select * from student_course_results  where  student_id is null
 
Plan hash value: 4267664529
 
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        |       |       |     1 (100)|          |
|*  1 |  FILTER            |                        |       |       |            |          |
|   2 |   TABLE ACCESS FULL| STUDENT_COURSE_RESULTS |     1 |    65 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

23 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 14u9w893qdv0f, child number 0
-------------------------------------
select * from student_course_results where student_id is null

Plan hash value: 4267664529

---------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 1 (100)\| \|
\|* 1 \| FILTER \| \| \| \| \| \|
\| 2 \| TABLE ACCESS FULL\| STUDENT_COURSE_RESULTS \| 1 \| 65 \| 2 (0)\| 00:00:01 \|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NULL IS NOT NULL)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 64

select * from student_course_results 
where  course_id is null

no data found

Statement 65

The same optimization does NOT take place for check constraints. The database will full scan the table, applying the condition to every row.

..but not for CHECK NOT NULL

select *    
from   dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'TYPICAL LAST +PREDICATE')


PLAN_TABLE_OUTPUT
SQL_ID  gx91bmy0z7phz, child number 0
-------------------------------------
select * from student_course_results  where  course_id is null
 
Plan hash value: 536234621
 
--------------------------------------------------------------------------------------------
| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                        |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| STUDENT_COURSE_RESULTS |     1 |    65 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("COURSE_ID" IS NULL)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

22 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID gx91bmy0z7phz, child number 0
-------------------------------------
select * from student_course_results where course_id is null

Plan hash value: 536234621

--------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
--------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 2 (100)\| \|
\|* 1 \| TABLE ACCESS FULL\| STUDENT_COURSE_RESULTS \| 1 \| 65 \| 2 (0)\| 00:00:01 \|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("COURSE_ID" IS NULL)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 66

Allow this column to be optional again

Remove NOT NULL constraint

alter table student_course_results  
  modify ( student_id null )

Table altered.

Statement 67

Allow this column to be optional again

Remove CHECK constraint

alter table student_course_results  
  drop constraint stcr_course_nn

Table altered.

Statement 68

If you have a group of columns that all need to be either NULL or NOT NULL, then you have to use a CHECK constraint.

Multi-column optionality

alter table student_course_results  
  add constraint stcr_student_course_both_neither_nn  
  check (  
    coalesce ( student_id, course_id ) is null  
    or ( student_id is not null and course_id is not null )  
  )

Table altered.

Statement 69

insert into student_course_results values ( 1, 1, 1, 1, 0 )

1 row(s) inserted.

Statement 70

insert into student_course_results values ( 2, null, null, 1, 0 )

1 row(s) inserted.

Statement 71

insert into student_course_results values ( 3, 99, null, 1, 0 )

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

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

Statement 72

insert into student_course_results values ( 4, null, 99, 1, 0 )

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

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

Statement 73

The all-or-none CHECK constraint ensures that the FK can no longer store orphaned values.

select * from student_course_results


STUDENT_COURSE_RESULT_ID STUDENT_ID COURSE_ID EXAM_ID PERCENT_CORRECT
1 1 1 1 0
2  -  - 1 0

2 rows selected.

STUDENT_COURSE_RESULT_ID	STUDENT_ID	COURSE_ID	EXAM_ID	PERCENT_CORRECT
1	1	1	1	0
2	-	-	1	0

Statement 74

CHECK constraints can enforce any deterministic condition

alter table student_course_results    
  add constraint stcr_percent_correct_0_to_100   
  check ( percent_correct between 0 and 100 )

Table altered.

Statement 75

truncate table student_course_results

Table truncated.

Statement 76

insert into student_course_results values ( 1, 1, 1, 2, -9999 )

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

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

Statement 77

insert into student_course_results values ( 2, 1, 1, 3,  9999 )

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

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

Statement 78

CHECK constraints only reject rows where the condition is FALSE. If the column is optional this can allow unwanted NULLs.

insert into student_course_results values ( 3, 1, 1, 4,  null )

1 row(s) inserted.

Statement 79

NULL values for PERCENT_CORRECT fail the law the excluded middle - that either the condition or its negation (NOT) are true.

Law of excluded middle failure

select * from student_course_results  
where  percent_correct between 0 and 100  
or     percent_correct not between 0 and 100

no data found

Statement 80

select * from student_course_results


STUDENT_COURSE_RESULT_ID STUDENT_ID COURSE_ID EXAM_ID PERCENT_CORRECT
3 1 1 4  -

STUDENT_COURSE_RESULT_ID	STUDENT_ID	COURSE_ID	EXAM_ID	PERCENT_CORRECT
3	1	1	4	-

Statement 81

select * from student_course_results 
where  percent_correct < 0

no data found

Statement 82

The check constraint means the optimizer knows there can be no rows WHERE PERCENT_CORRECT < 0. So it adds a FILTER operation above the table scan. Notice the cost is zero.

Check constraint optimizations

select *    
from   dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'TYPICAL LAST +PREDICATE')


PLAN_TABLE_OUTPUT
SQL_ID  c5wmsf5u91k9s, child number 0
-------------------------------------
select * from student_course_results  where  percent_correct < 0
 
Plan hash value: 4267664529
 
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                        |       |       |     1 (100)|          |
|*  1 |  FILTER            |                        |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| STUDENT_COURSE_RESULTS |     1 |    65 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)
   2 - filter("PERCENT_CORRECT"<0)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

24 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID c5wmsf5u91k9s, child number 0
-------------------------------------
select * from student_course_results where percent_correct < 0

Plan hash value: 4267664529

---------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 1 (100)\| \|
\|* 1 \| FILTER \| \| \| \| \| \|
\|* 2 \| TABLE ACCESS FULL\| STUDENT_COURSE_RESULTS \| 1 \| 65 \| 3 (0)\| 00:00:01 \|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NULL IS NOT NULL)
2 - filter("PERCENT_CORRECT"<0)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 83

Reset table

drop table student_course_results   
  cascade constraints purge

Table dropped.

Statement 84

create table student_course_results ( 
  student_id integer, 
  course_id  integer, 
  exam_id    integer, 
  percent_correct number 
)

Table created.

Statement 85

Load table with junk data

insert into student_course_results  
  select student_id, course_id,   
         1, round ( dbms_random.value ( -100, 1000 ) )  
  from   students   
  cross  join courses  
  /* Insert duplicates */  
  cross  join ( select * from dual connect by level <= 2 )

12 row(s) inserted.

Statement 86

select * from student_course_results


STUDENT_ID COURSE_ID EXAM_ID PERCENT_CORRECT
1 1 1 49
1 2 1 588
1 1 1 555
1 2 1 573
4 1 1 513
4 2 1 180
4 1 1 627
4 2 1 519
5 1 1 460
5 2 1 252
5 1 1 275
5 2 1 56

12 rows selected.

STUDENT_ID	COURSE_ID	EXAM_ID	PERCENT_CORRECT
1	1	1	49
1	2	1	588
1	1	1	555
1	2	1	573
4	1	1	513
4	2	1	180
4	1	1	627
4	2	1	519
5	1	1	460
5	2	1	252
5	1	1	275
5	2	1	56

Statement 87

You can't add constraints if there's invalid data by default

alter table student_course_results  
  add constraint stcr_percent_c  
  check ( percent_correct between 0 and 100 )

ORA-02293: cannot validate (SQL_LBZXMQQATQMTXRTDILZWERGGD.STCR_PERCENT_C) - check constraint violated

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

Statement 88

If there are uncommitted transactions on the table in other sessions, you can't add validated constraints.

Adding constraints is a blocking operation

declare   
  procedure add_constraint as   
    pragma autonomous_transaction;   
  begin   
    execute immediate '   
      alter table student_course_results   
        add constraint stcr_percent_c   
        check ( percent_correct between 0 and 100 )   
    ';   
  end ;   
begin   
  insert into student_course_results    
    values ( 1, 1, 1, 999 );   
       
  add_constraint();   
end;

ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 5
ORA-06512: at line 15
ORA-06512: at "SYS.DBMS_SQL", line 1721

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

Statement 89

Creating a constraint NOVALIDATE does not check existing data. It is also an online operation.

NOVALIDATE constraints

alter table student_course_results   
  add constraint stcr_percent_c   
  check ( percent_correct between 0 and 100 )   
  novalidate

Table altered.

Statement 90

With the NOVALIDATE constraint in place, you can't add new rows that violate it. There can still be existing rows which fail to conform.

insert into student_course_results    
  values ( 1, 1, 1, 9999 )

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

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

Statement 91

select * from student_course_results  
where  percent_correct < 0

no data found

Statement 92

Fix the invalid data

update student_course_results   
set    percent_correct = 0   
where  percent_correct not between 0 and 100

10 row(s) updated.

Statement 93

This means that all rows in the table must conform to the constraint

You can now VALIDATE the constraint

alter table student_course_results   
  modify constraint stcr_percent_c   
  validate

Table altered.

Statement 94

select * from student_course_results  
where  percent_correct < 0

no data found

Statement 95

Adding a NOVALIDATE NOT NULL

alter table student_course_results   
  modify student_id    
  constraint stcr_student_nn not null    
  novalidate

Table altered.

Statement 96

Validating NOT NULL constraints

alter table student_course_results   
  modify constraint stcr_student_nn   
  validate

Table altered.

Statement 97

By default the database tries to create a unique index to policy a primary key. So trying to add a NOVALIDATE primary key fails. Unique constraints have the same issue.

NOVALIDATE Primary key

alter table student_course_results   
  add constraint student_course_result_pk   
  primary key (     
    student_id, course_id, exam_id    
  )   
  novalidate

ORA-02437: cannot validate (SQL_LBZXMQQATQMTXRTDILZWERGGD.STUDENT_COURSE_RESULT_PK) - primary key violated

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

Statement 98

To add an unvalidated primary key (or unique constraint), you need to police it with a non-unique index. With the USING INDEX clause you can list an existing index or create a new one.

NOVALIDATE Primary key

alter table student_course_results   
  add constraint student_course_result_pk   
  primary key (    
    student_id, course_id, exam_id    
  )   
  using index (   
    /* Can create new index or use existing here */   
    create index stcr_student_course_date_i   
      on student_course_results (    
      student_id, course_id, exam_id    
    )    
  )   
  novalidate

Table created.

Statement 99

insert into student_course_results values ( 5, 5, 5, 1 )

1 row(s) inserted.

Statement 100

insert into student_course_results values ( 5, 5, 5, 99 )

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

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

Statement 101

Remove the duplicate rows

delete student_course_results   
where  rowid not in (    
  select min ( rowid )   
  from   student_course_results   
  group  by student_id, course_id, exam_id   
)

6 row(s) deleted.

Statement 102

You can now validate the PK

alter table student_course_results   
  modify constraint student_course_result_pk   
  validate

Table altered.

Statement 103

CREATE-TABLE-AS-SELECT only copies NOT NULL constraints from the source table.

CTAS constraints

create table student_course_results_backup as    
  select * from student_course_results

Table created.

Statement 104

select * from student_courses


STUDENT_ID COURSE_ID
1 1

STUDENT_ID	COURSE_ID
1	1

Statement 105

select constraint_name, table_name, constraint_type, search_condition   
from   user_constraints  
where  table_name like 'STUDENT_COURSE_RESULT%'  
order  by 1, 2


CONSTRAINT_NAME TABLE_NAME CONSTRAINT_TYPE SEARCH_CONDITION
STCR_PERCENT_C STUDENT_COURSE_RESULTS C  percent_correct between 0 and 100 
STCR_STUDENT_NN STUDENT_COURSE_RESULTS C "STUDENT_ID" IS NOT NULL
STUDENT_COURSE_RESULT_PK STUDENT_COURSE_RESULTS P  - 
SYS_C00114593165 STUDENT_COURSE_RESULTS_BACKUP C "STUDENT_ID" IS NOT NULL

4 rows selected.

CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	SEARCH_CONDITION
STCR_PERCENT_C	STUDENT_COURSE_RESULTS	C	percent_correct between 0 and 100
STCR_STUDENT_NN	STUDENT_COURSE_RESULTS	C	"STUDENT_ID" IS NOT NULL
STUDENT_COURSE_RESULT_PK	STUDENT_COURSE_RESULTS	P	-
SYS_C00114593165	STUDENT_COURSE_RESULTS_BACKUP	C	"STUDENT_ID" IS NOT NULL

Statement 106

You can define constraints when making a table using CREATE-TABLE-AS-SELECT. Do this in the column list after the new table name.

Defining constraints with CTAS

create table student_course_results_backup_constraints (   
  student_id,   
  course_id,   
  exam_id not null ,   
  percent_correct check ( percent_correct between 0 and 100 ),   
  primary key ( student_id, course_id, exam_id )   
) as    
  select * from student_course_results   
  where  nvl ( percent_correct, -1 ) between 0 and 100

Table created.

Statement 107

select constraint_name, table_name, constraint_type, search_condition   
from   user_constraints  
where  table_name = 'STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS'  
order  by 1, 2


CONSTRAINT_NAME TABLE_NAME CONSTRAINT_TYPE SEARCH_CONDITION
SYS_C00114593166 STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS C "STUDENT_ID" IS NOT NULL
SYS_C00114593167 STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS C "EXAM_ID" IS NOT NULL
SYS_C00114593168 STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS C  percent_correct between 0 and 100 
SYS_C00114593169 STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS P  - 

4 rows selected.

A beginner's guide to database constraints - SQL Office Hours Feb 2023 script

Additional Information

STUDENT_ID	COURSE_ID	EXAM_ID	PERCENT_CORRECT
1	1	1	49
1	2	1	588
1	1	1	555
1	2	1	573
4	1	1	513
4	2	1	180
4	1	1	627
4	2	1	519
5	1	1	460
5	2	1	252
5	1	1	275
5	2	1	56

CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	SEARCH_CONDITION
SYS_C00114593166	STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS	C	"STUDENT_ID" IS NOT NULL
SYS_C00114593167	STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS	C	"EXAM_ID" IS NOT NULL
SYS_C00114593168	STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS	C	percent_correct between 0 and 100
SYS_C00114593169	STUDENT_COURSE_RESULTS_BACKUP_CONSTRAINTS	P	-

STUDENT_ID	COURSE_ID	EXAM_ID	PERCENT_CORRECT
1	1	1	49
1	2	1	588
1	1	1	555
1	2	1	573
4	1	1	513
4	2	1	180
4	1	1	627
4	2	1	519
5	1	1	460
5	2	1	252
5	1	1	275
5	2	1	56

STUDENT_ID	COURSE_ID	EXAM_ID	PERCENT_CORRECT
1	1	1	49
1	2	1	588
1	1	1	555
1	2	1	573
4	1	1	513
4	2	1	180
4	1	1	627
4	2	1	519
5	1	1	460
5	2	1	252
5	1	1	275
5	2	1	56