How to change constraints - SQL Ask TOM Office Hours July 2023

View All Scripts Login to Run Script

Script Name How to change constraints - SQL Ask TOM Office Hours July 2023
Description Scripts for the SQL Office Hours session on "How to modify constraints in Oracle Database" https://asktom.oracle.com/pls/apex/asktom.search?oh=21184. For more details read the blog post https://blogs.oracle.com/sql/post/split-comma-separated-values-into-rows-in-oracle-database
Area SQL General / DDL
Contributor Chris Saxon (Oracle)
Created Thursday July 13, 2023

Statement 1

create table invoices ( 
  invoice_id       integer not null 
    constraint invoice_pk primary key, 
  customer_id      integer not null, 
  invoice_datetime timestamp not null, 
  status           varchar2(10) not null, 
  constraint inv_status_c check ( 
    status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED' ) 
  ) 
)

Table created.

Statement 2

create table invoice_items ( 
  invoice_id  
    constraint init_invoice_fk  
      references invoices 
    not null,  
  item_number     integer not null, 
  product_id      integer not null, 
  order_id        integer not null, 
  quantity        integer not null, 
  unit_price      number not null, 
  constraint invoice_items_pk 
    primary key ( invoice_id, item_number ), 
  constraint inv_order_product_u 
    unique ( order_id, product_id ), 
  constraint init_qty_gt_zero_c  
    check ( quantity > 0 ) 
)

Table created.

Statement 3

insert into invoices  
  values ( 1, 1, timestamp'2023-01-01 00:00:00', 'NEW' )

1 row(s) inserted.

Statement 4
```
commit
```
```
Statement processed.
```

Statement 5

Adding the new constraint without validation changes this to non-blocking DDL, enabling you to add it online.

Add new, unvalidated constraint

alter table invoices  
  add constraint inv_status_c_new 
  check ( 
    status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED', 'VOID' ) 
  ) 
  novalidate

Table altered.

Statement 6

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


CONSTRAINT_NAME VALIDATED SEARCH_CONDITION
INV_STATUS_C VALIDATED  
    status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED' ) 
  
INV_STATUS_C_NEW NOT VALIDATED  
    status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED', 'VOID' ) 
  
SYS_C00128236024 VALIDATED "INVOICE_ID" IS NOT NULL
SYS_C00128236025 VALIDATED "CUSTOMER_ID" IS NOT NULL
SYS_C00128236026 VALIDATED "INVOICE_DATETIME" IS NOT NULL
SYS_C00128236027 VALIDATED "STATUS" IS NOT NULL

6 rows selected.

Statement 7
Where possible, it's good practice to validate the new constraint. This checks all existing rows conform to it. This enables the optimizer to use it when picking plans.
Validate new constraint
```
alter table invoices  
  modify constraint inv_status_c_new 
  validate
```
```
Table altered.
```

CONSTRAINT_NAME	VALIDATED	SEARCH_CONDITION
INV_STATUS_C	VALIDATED	status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED' )
INV_STATUS_C_NEW	NOT VALIDATED	status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED', 'VOID' )
SYS_C00128236024	VALIDATED	"INVOICE_ID" IS NOT NULL
SYS_C00128236025	VALIDATED	"CUSTOMER_ID" IS NOT NULL
SYS_C00128236026	VALIDATED	"INVOICE_DATETIME" IS NOT NULL
SYS_C00128236027	VALIDATED	"STATUS" IS NOT NULL

Statement 8

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


CONSTRAINT_NAME VALIDATED SEARCH_CONDITION
INV_STATUS_C VALIDATED  
    status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED' ) 
  
INV_STATUS_C_NEW VALIDATED  
    status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED', 'VOID' ) 
  
SYS_C00128236024 VALIDATED "INVOICE_ID" IS NOT NULL
SYS_C00128236025 VALIDATED "CUSTOMER_ID" IS NOT NULL
SYS_C00128236026 VALIDATED "INVOICE_DATETIME" IS NOT NULL
SYS_C00128236027 VALIDATED "STATUS" IS NOT NULL

6 rows selected.

Statement 9
With the new constraint in place, you can now remove the old one. The ONLINE clause makes this non-blocking DDL.
Remove the old constraint
```
alter table invoices  
  drop constraint inv_status_c 
  online
```
```
Table altered.
```
Statement 10
You can rename the new constraint to use the original's name if desired. Note this is blocking DDL.
Change constraint name
```
alter table invoices  
  rename constraint inv_status_c_new 
  to inv_status_c
```
```
Table altered.
```

CONSTRAINT_NAME	VALIDATED	SEARCH_CONDITION
INV_STATUS_C	VALIDATED	status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED' )
INV_STATUS_C_NEW	VALIDATED	status in ( 'NEW', 'PAID', 'CANCELLED', 'REFUNDED', 'VOID' )
SYS_C00128236024	VALIDATED	"INVOICE_ID" IS NOT NULL
SYS_C00128236025	VALIDATED	"CUSTOMER_ID" IS NOT NULL
SYS_C00128236026	VALIDATED	"INVOICE_DATETIME" IS NOT NULL
SYS_C00128236027	VALIDATED	"STATUS" IS NOT NULL

Statement 11

Note they all have system-generated names. You need to look this up if you want to drop the constraint online.

View the NOT NULL constraints

select constraint_name, column_name 
from   user_constraints 
join   user_cons_columns 
using  ( table_name, constraint_name ) 
where  table_name = 'INVOICES' 
and    search_condition_vc like '%NOT NULL%'


CONSTRAINT_NAME COLUMN_NAME
SYS_C00128236024 INVOICE_ID
SYS_C00128236025 CUSTOMER_ID
SYS_C00128236026 INVOICE_DATETIME
SYS_C00128236027 STATUS

4 rows selected.

CONSTRAINT_NAME	COLUMN_NAME
SYS_C00128236024	INVOICE_ID
SYS_C00128236025	CUSTOMER_ID
SYS_C00128236026	INVOICE_DATETIME
SYS_C00128236027	STATUS

Statement 12

To drop a constraint online, you need to know its name! This uses dynamic SQL to find a column's NOT NULL constraint name and remove it.

Make a column optional

declare 
  stmt            clob; 
  constraint_name varchar2(ora_max_name_len); 
begin 
  select constraint_name 
  into   constraint_name  
  from   user_constraints 
  join   user_cons_columns 
  using  ( table_name, constraint_name ) 
  where  table_name = 'INVOICES' 
  and    search_condition_vc like '%NOT NULL%' 
  and    column_name = 'STATUS'; 
 
  stmt := 'alter table invoices 
    drop constraint ' || constraint_name || ' 
    online'; 
  execute immediate stmt; 
end;

Table dropped.

Statement 13
This makes STATUS mandatory again, this time with a named constraint. As before, the NOVALIDATE clause makes this non-blocking DDL.
Make a column mandatory
```
alter table invoices 
  modify status 
  constraint inv_status_nn 
  not null 
  novalidate
```
```
Table altered.
```
Statement 14
This ensures that STATUS is NOT NULL for all rows in the table
Validate NOT NULL
```
alter table invoices 
  modify constraint inv_status_nn  
  validate
```
```
Table altered.
```
Statement 15
To add or remove columns from a unique constraint while the applications are live, first create a unique index online.
Add columns to a unique constraint
```
create unique index inv_order_product_inv_ui 
  on invoice_items ( order_id, product_id, invoice_id ) 
  online
```
```
Index created.
```

Statement 16

You can now add the unique constraint that uses the new index built in the previous step

Add unique constraint using index

alter table invoice_items  
  add constraint inv_order_product_inv_u 
  unique ( order_id, product_id, invoice_id )  
  using index inv_order_product_inv_ui 
  novalidate

Table altered.

Statement 17
The unique index already stops duplicates, so this step is instant.
Validate unique constraint
```
alter table invoice_items  
  modify constraint inv_order_product_u 
  validate
```
```
Table altered.
```

Statement 18

Remove old unique constraint

alter table invoice_items 
  drop constraint inv_order_product_u 
  online

Table altered.

Statement 19
There is a foreign key pointing to the primary key, so this operation fails.
Changing primary key
```
alter table invoices  
  drop primary key
```
```
ORA-02273: this unique/primary key is referenced by some foreign keys 
```
More Details: https://docs.oracle.com/error-help/db/ora-02273

Statement 20

select * from user_constraints 
where  r_constraint_name = 'INVOICE_PK'


OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION SEARCH_CONDITION_VC R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED ORIGIN_CON_ID
SQL_XSIYZTLIUNRUVDYIMDLSKNBOC INIT_INVOICE_FK R INVOICE_ITEMS  -  - SQL_XSIYZTLIUNRUVDYIMDLSKNBOC INVOICE_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME  -  - 13-JUL-23  -  -  -  - 3

Statement 21
The CASCADE clause removes any foreign keys pointing to this primary key. The KEEP INDEX preserves the unique index used by the constraint.
Force primary key removal
```
alter table invoices  
  drop primary key 
  cascade 
  keep index
```
```
Table altered.
```

Statement 22

select index_name, uniqueness  
from   user_indexes 
where  table_name = 'INVOICES'


INDEX_NAME UNIQUENESS
INVOICE_PK UNIQUE

Statement 23
This replaces the primary key with a unique constraint. This enables you to add a new primary key to the table.
Add unique constraint using old primary key index
```
alter table invoices 
  add unique ( invoice_id ) 
  using index invoice_pk 
  novalidate
```
```
Table altered.
```
Statement 24
This recreates the foreign key dropped when the primary key was removed. This time it points to the unique constraint and has the ON DELETE CASCADE & DEFERRABLE properties.
Recreate foreign key
```
alter table invoice_items  
  add constraint inv_cust_date_fk  
  foreign key ( invoice_id ) 
  references invoices ( invoice_id ) 
  on delete cascade 
  deferrable
```
```
Table altered.
```
Statement 25
You can't modify the ON DELETE or DEFERRABLE properties for a foreign key. You also can't have two duplicate foreign keys on a column. To change these properties online you can use DBMS_redefinition.
Can't have duplicate foreign keys
```
alter table invoice_items  
  add constraint inv_cust_date_fk  
  foreign key ( invoice_id ) 
  references invoices ( invoice_id )
```
```
ORA-02275: such a referential constraint already exists in the table 
```
More Details: https://docs.oracle.com/error-help/db/ora-02275

INDEX_NAME	UNIQUENESS
INVOICE_PK	UNIQUE

Statement 26

It is possible to have identical check constraints on a table!

Duplicate check constraints

alter table invoice_items  
  add constraint init_qty_gt_zero_c_copy 
  check ( quantity > 0 )

Table altered.

Statement 27

select constraint_name, search_condition_vc  
from   user_constraints 
where  table_name = 'INVOICE_ITEMS'


CONSTRAINT_NAME SEARCH_CONDITION_VC
INV_ORDER_PRODUCT_INV_U  - 
INV_CUST_DATE_FK  - 
SYS_C00128236030 "INVOICE_ID" IS NOT NULL
SYS_C00128236031 "ITEM_NUMBER" IS NOT NULL
SYS_C00128236032 "PRODUCT_ID" IS NOT NULL
SYS_C00128236033 "ORDER_ID" IS NOT NULL
SYS_C00128236034 "QUANTITY" IS NOT NULL
SYS_C00128236035 "UNIT_PRICE" IS NOT NULL
INIT_QTY_GT_ZERO_C  quantity > 0 
INVOICE_ITEMS_PK  - 
INIT_QTY_GT_ZERO_C_COPY  quantity > 0 

11 rows selected.

CONSTRAINT_NAME	SEARCH_CONDITION_VC
INV_ORDER_PRODUCT_INV_U	-
INV_CUST_DATE_FK	-
SYS_C00128236030	"INVOICE_ID" IS NOT NULL
SYS_C00128236031	"ITEM_NUMBER" IS NOT NULL
SYS_C00128236032	"PRODUCT_ID" IS NOT NULL
SYS_C00128236033	"ORDER_ID" IS NOT NULL
SYS_C00128236034	"QUANTITY" IS NOT NULL
SYS_C00128236035	"UNIT_PRICE" IS NOT NULL
INIT_QTY_GT_ZERO_C	quantity > 0
INVOICE_ITEMS_PK	-
INIT_QTY_GT_ZERO_C_COPY	quantity > 0

Statement 28

This is a copy of INVOICE_ITEMS, but with an updated foreign key without ON DELETE CASCADE or DEFERRABLE.

Temporary table for redefinition

create table invoice_items_tmp ( 
  invoice_id  
    constraint init_invoice_fk_tmp  
      references invoices ( invoice_id ) 
    not null,  
  item_number     integer not null, 
  product_id      integer not null, 
  order_id        integer not null, 
  quantity        integer not null, 
  unit_price      number not null, 
  constraint invoice_items_pk_tmp 
    primary key ( invoice_id, item_number ), 
  constraint inv_order_product_u_tmp 
    unique ( order_id, product_id ), 
  constraint init_qty_gt_zero_c_tmp 
    check ( quantity > 0 ) 
)

Table created.

Statement 29

select table_name, constraint_name, constraint_type, delete_rule, deferrable 
from   user_constraints 
where  table_name like 'INVOICE_ITEMS%' 
and    constraint_type = 'R'


TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE DELETE_RULE DEFERRABLE
INVOICE_ITEMS_TMP INIT_INVOICE_FK_TMP R NO ACTION NOT DEFERRABLE
INVOICE_ITEMS INV_CUST_DATE_FK R CASCADE DEFERRABLE

2 rows selected.

TABLE_NAME	CONSTRAINT_NAME	CONSTRAINT_TYPE	DELETE_RULE	DEFERRABLE
INVOICE_ITEMS_TMP	INIT_INVOICE_FK_TMP	R	NO ACTION	NOT DEFERRABLE
INVOICE_ITEMS	INV_CUST_DATE_FK	R	CASCADE	DEFERRABLE

Statement 30

Redefine the constraints online

declare   
  l_num_errors pls_integer;   
begin   
  dbms_redefinition.can_redef_table(SYS_CONTEXT('userenv','current_schema'), 'invoice_items');   
  dbms_redefinition.start_redef_table(SYS_CONTEXT('userenv','current_schema'), 'invoice_items', 'invoice_items_tmp');    
 
  /*  Comment back in to copy grants & triggers if necessary 
  dbms_redefinition.copy_table_dependents(   
    uname             => SYS_CONTEXT('userenv','current_schema'),   
    orig_table        => 'INVOICE_ITEMS',   
    int_table         => 'INVOICE_ITEMS_TMP',   
    copy_indexes      => 0,   
    copy_constraints  => false,   
    ignore_errors     => true,   
    num_errors        => l_num_errors);    
  */     
 
  if l_num_errors > 0 then   
       
    dbms_redefinition.abort_redef_table(SYS_CONTEXT('userenv','current_schema'), 'invoice_items', 'invoice_items_tmp');   
    raise_application_error ( -20001, 'Redef problem' );   
       
  else   
     
    dbms_redefinition.sync_interim_table(SYS_CONTEXT('userenv','current_schema'), 'invoice_items', 'invoice_items_tmp');    
    dbms_redefinition.finish_redef_table(SYS_CONTEXT('userenv','current_schema'), 'invoice_items', 'invoice_items_tmp');   
     
  end if;   
   
end;

Statement processed.

Statement 31

The database has now switched the original table and temporary table over.

select table_name, constraint_name, constraint_type, delete_rule, deferrable 
from   user_constraints 
where  table_name like 'INVOICE_ITEMS%' 
and    constraint_type = 'R'


TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE DELETE_RULE DEFERRABLE
INVOICE_ITEMS INIT_INVOICE_FK_TMP R NO ACTION NOT DEFERRABLE
INVOICE_ITEMS_TMP INV_CUST_DATE_FK R CASCADE DEFERRABLE

2 rows selected.

TABLE_NAME	CONSTRAINT_NAME	CONSTRAINT_TYPE	DELETE_RULE	DEFERRABLE
INVOICE_ITEMS	INIT_INVOICE_FK_TMP	R	NO ACTION	NOT DEFERRABLE
INVOICE_ITEMS_TMP	INV_CUST_DATE_FK	R	CASCADE	DEFERRABLE

Additional Information

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