I am writing some code that will disable and enable foreign keys on a table. It appears to run fine. When I check the status of the Foreign key immediately executing the code the status appears to be unchanged with one query and changed with a different query.
I can't seem to figure out what the problem is and was hoping someone can help me out and explain this anomaly and or suggest a fix to my code.
Below is my test CASE along with sample data to show what I have done.
CREATE TABLE customers
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Joseph', 'Zaza' FROM DUAL UNION ALL
SELECT 5, 'Jerry', 'Torchiano' FROM DUAL;
ALTER TABLE customers
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
CREATE TABLE items
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;
ALTER TABLE items
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);
create table purchases(
ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
customer_id number,
PRODUCT_ID NUMBER,
QUANTITY NUMBER,
purchase_date timestamp
);
insert into purchases (customer_id, product_id, quantity, purchase_date)
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual
CONNECT BY LEVEL <= 15 UNION ALL
select 1, 101,3, date '2023-03-29' + level * interval '2' day from dual
connect by level <= 12
union all
select 2, 101,2, date '2023-01-15' + level * interval '8' hour from dual
connect by level <= 15
union all
select 2, 102,2,date '2023-04-13' + level * interval '1 1' day to hour from dual
connect by level <= 11
union all
select 3, 101,2, date '2023-02-01' + level * interval '1 05:03' day to minute from dual
connect by level <= 10
union all
select 3, 101,1, date '2023-04-22' + level * interval '23' hour from dual
connect by level <= 23
union all
select 3, 100,1, date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
connect by level <= 15
union all
select 4, 102,1, date '2023-01-01' + level * interval '5' hour from dual
connect by level <= 60;
ALTER TABLE purchases
ADD CONSTRAINT order_pk PRIMARY KEY (order_id);
ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);
/* both queries fine here */
SELECT '"' || a.owner
|| '"."'
|| a.table_name
|| '"' AS full_table_name,
a.constraint_name,
b.status
FROM user_constraints a
JOIN user_constraints b
ON ( a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner )
WHERE a.constraint_type = 'R'
AND b.status = 'ENABLED'
ORDER BY 1
FULL_TABLE_NAME
CONSTRAINT_NAME STATUS
"XXX"."PURCHASES" ITEMS_FK ENABLED
"XXX"."PURCHASES" CUSTOMERS_FK ENABLED
select owner, table_name, r_constraint_name, status
from user_constraints
where constraint_type = 'R';
OWNER TABLE_NAME R_CONSTRAINT_NAME STATUS
XXX PURCHASES CUSTOMERS_PK ENABLED
XXX PURCHASES ITEMS_PK ENABLED
/* disable foreign keys */
BEGIN
FOR r IN (
SELECT '"' || a.owner
|| '"."'
|| a.table_name
|| '"' AS full_table_name,
a.constraint_name
FROM user_constraints a
JOIN user_constraints b
ON ( a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner )
WHERE a.constraint_type = 'R'
AND b.status = 'ENABLED'
ORDER BY 1 ) LOOP
dbms_output.put_line ( 'Disable the constraint ' || r.constraint_name ||' (on table ' || r.full_table_name || ')' ) ;
dbms_utility.exec_ddl_statement ( 'alter table ' || r.full_table_name || ' disable constraint ' || r.constraint_name ) ;
END LOOP ;
END ;
/
Statement processed.
Disable the constraint ITEMS_FK (on table "XXX"."PURCHASES")
Disable the constraint CUSTOMERS_FK (on table "XXX"."PURCHASES")
/* status different between queries ????. Problem here!!!
*/
SELECT '"' || a.owner
|| '"."'
|| a.table_name
|| '"' AS full_table_name,
a.constraint_name,
b.status
FROM user_constraints a
JOIN user_constraints b
ON ( a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner )
WHERE a.constraint_type = 'R'
AND b.status = 'ENABLED'
ORDER BY 1
FULL_TABLE_NAME
CONSTRAINT_NAME STATUS
"XXX"."PURCHASES" ITEMS_FK ENABLED
"XXX"."PURCHASES" CUSTOMERS_FK ENABLED
select owner, table_name, r_constraint_name, status
from user_constraints
where constraint_type = 'R';
OWNER TABLE_NAME R_CONSTRAINT_NAME STATUS
XXX PURCHASES CUSTOMERS_PK DISABLED
XXX PURCHASES ITEMS_PK DISABLED
Those are 2 different queries and they are correctly showing the results. you're assuming that you are seeing the status of the foreign key constraint but... that is not the case. Add some additional columns to the query and it all becomes clear.
SELECT '"' || a.owner
|| '"."'
|| a.table_name
|| '"' AS full_table_name,
a.constraint_name,
a.status,
b.constraint_name,
b.table_name,
b.status
FROM user_constraints a
JOIN user_constraints b
ON ( a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner )
WHERE a.constraint_type = 'R'
AND b.status = 'ENABLED'
ORDER BY 1;
"ACDC"."PURCHASES" ITEMS_FK DISABLED ITEMS_PK ITEMS ENABLED
"ACDC"."PURCHASES" CUSTOMERS_FK. DISABLED CUSTOMERS_PK CUSTOMERS ENABLED
The column b.status is the status of the referenced constraint, not the status of the foreign key constraint. The referenced constraint is the primary key value of the 2 tables and the status is "enabled" since you didn't touch those....
To find referenced constraints, just use that other query you are showing
select owner, table_name, r_constraint_name, status
from user_constraints
where constraint_type = 'R';
But... I would not take this route. What if there is a constraint that is already disabled before you run your script ? That constraint will then be enabled again by your "enable" script. Much safer to create a table where you store the name of the constraints you have disabled so you know which ones to enable again afterwards.