Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jul 28, 2009

Foreign Key Constraints Revisited

By DatabaseJournal.com Staff

by JP Vijaykumar

Many times readers send questions about how to handle foreign key constraints, when the primary key constraints on the parent tables have been altered.

Some readers asked for scripts to disable and re-enable foreign key constraints on all of the tables in a schema, as full/partial schema refreshes across environments are a regular requirement in databases.

I have simulated this test case to explain the requirements:

create user jp identified by jp account unlock
default tablespace users temporary tablespace temp
quota unlimited on users;


create table jp.manager (mgr_num number,mgr_name varchar2(20));

alter table jp.manager add 
	(constraint manager_pk primary key (mgr_num));

SQL> desc jp.manager
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MGR_NUM                                   NOT NULL NUMBER
 MGR_NAME                                           VARCHAR2(20)

The primary key constraint manager_pk is enforcing a not null constraint on the jp.manager.mgr_num column.

SQL> insert into jp.manager values(1,'BABU');

1 row created.

SQL> commit;

Commit complete.



create table jp.employee(emp_num number,
emp_name varchar2(20), mgr_num number);

alter table jp.employee add (constraint employee_fk  
foreign key (mgr_num) references jp.manager(mgr_num));

SQL> desc jp.employee
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_NUM                                            NUMBER
 EMP_NAME                                           VARCHAR2(20)
 MGR_NUM                                            NUMBER

By default, a foreign key constraint does not enforce a not null constraint on the child table's column, jp.employee.mgr_num.

What if the MGR_NUM column in JP.EMPLOYEE table is nullable?

SQL> insert into jp.employee values(1,'BAJU',2);
insert into jp.employee values(1,'BAJU',2)
*
ERROR at line 1:
ORA-02291: integrity constraint 
	(JP.WORKER_FK) violated - parent key not found


SQL>  insert into jp.employee values(1,'BAJU',null);

1 row created.

The foreign key constraint employee_fk did not allow us to insert value 2 into the

jp.employee.mgr_num column, instead giving us the error "parent key not found".

However, the said foreign key constraint employee_fk allowed us to insert a null value in the jp.employee.mgr_num column. How can this be possible?

As per RDBMS definition, the value of null is unknown or undefined. Null is not equal to anything. A null is not equal to another null.

As such, Oracle does not know how to check for a matching record in the parent table jp.manager.mgr_num column, that matches with the null value in the child in the jp.employee.mgr_num column.

To safeguard our data from this scenario, please enable a not null constraint on the jp.employee.mgr_num column.

truncate table jp.employee;

alter table jp.employee modify(mgr_num number not null);

insert into jp.employee values(1,'BAJU',1);
insert into jp.employee values(2,'BASU',1);
commit;

Let us drop the foreign key constraint on jp.employee table.

alter table jp.employee drop constraint employee_fk;

alter table jp.manager drop constraint manager_pk;

Now, a primary key constraint on mgr_num and mgr_name colums is added on the jp.manager table.

alter table jp.manager add (constraint manager_pk primary key (mgr_num,mgr_name));


SQL> desc jp.manager
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MGR_NUM                                   NOT NULL NUMBER
 MGR_NAME                                  NOT NULL VARCHAR2(20)

Now the prikery key constraint manager_pk on mgr_num and mgr_name of jp.manager table is enforcing not null constraints on both mgr_num and mgr_name columns.

Now let's recreate the emplyee_fk foreign key constraint on jp.employee table.

SQL> alter table jp.employee add (
constraint employee_fk foreign key (mgr_num) references jp.manager(mgr_num));  2
constraint employee_fk foreign key (mgr_num) references jp.manager(mgr_num))
                                                                 *
ERROR at line 2:
ORA-02270: no matching unique or primary key for this column-list

Since there is no primary key constraint or unique constraint enabled on the column jp.manager.mgr_num column exclusively, the employee_fk foreign key constraint could not be enabled on the jp.employee table.

Let's first add a unique constraint on the jp.manager.mgr_num column and later add the primary key constraint on jp.manager.mgr_num and jp.manager.mgr_name columns.

alter table jp.manager drop constraint manager_pk;

alter table jp.manager add (constraint manager_uk unique (mgr_num));

SQL>  desc jp.manager
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MGR_NUM                                            NUMBER
 MGR_NAME                                           VARCHAR2(20)

Only a primary key constraint enforces a not null constraint on the tables' columns,

a unique constraint does not enforce a not null constraint on the tables' columns.

To safeguard and validate the data, it is a good practice to enable a not null constraint on the columns, where a unique constraint is created.

Since the primary key constraint on mgr_num and mgr_name columns will enforce not null constraints, our requirement is met.

alter table jp.manager add (constraint manager_pk primary key (mgr_num,mgr_name));


SQL>   desc jp.manager
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MGR_NUM                                   NOT NULL NUMBER
 MGR_NAME                                  NOT NULL VARCHAR2(20)

Now we can add the employee_fk foreign key constraint on the jp.employee table.

alter table jp.employee add (constraint employee_fk foreign key 
(mgr_num) references jp.manager(mgr_num));

Now I want to truncate all the tables in JP's schema and refresh with import from the production db.

SQL> select count(1) from jp.manager;

  COUNT(1)
----------
         1

SQL> select count(1) from jp.employee;

  COUNT(1)
----------
         2

The following scripts disable all foreign key constraints and truncate the tables in a particular schema and re-enable the foreign key constraints.

Please make the necessary changes as per your specific requirements.

set serverout on size 1000000
declare
v_str varchar2(1000);
begin
for c1 in (select owner,table_name from dba_tables where owner='JP') loop
begin
for c2 in (select a.owner,a.table_name,a.constraint_name
            from all_constraints a, all_constraints b
            where a.constraint_type = 'R'
            and a.r_constraint_name = b.constraint_name
            and a.r_owner  = c1.owner
            and b.table_name = c1.table_name) loop
v_str:='alter table '||c2.owner||'.'||c2.table_name||
        ' disable constraint '
	||c2.constraint_name;
--dbms_output.put_line(v_str);
execute immediate v_str; 
end loop;
--execute immediate 'truncate table '||c1.owner||'.'||c1.table_name;
exception
when others then 
dbms_output.put_line(c1.table_name||' '||sqlerrm);
end;
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL>  select count(1) from jp.manager;

  COUNT(1)
----------
         0

SQL>  select count(1) from jp.employee;

  COUNT(1)
----------
         0


SQL> select constraint_name,status from dba_constraints
  2   where owner='JP' and table_name='EMPLOYEE';

CONSTRAINT_NAME                STATUS
------------------------------ --------
SYS_C0032318                   ENABLED
EMPLOYEE_FK                    DISABLED

I will refresh the JP's schema tables.

insert into jp.manager values(1,'BABU');
insert into jp.employee values(1,'BAJU',1);
insert into jp.employee values(2,'BASU',1);
commit;


set serverout on size 1000000
declare
v_str varchar2(1000);
begin
for c1 in (select owner,table_name from dba_tables where owner='JP') loop
begin
for c2 in (select a.owner,a.table_name,a.constraint_name
            from all_constraints a, all_constraints b
            where a.constraint_type = 'R'
            and a.r_constraint_name = b.constraint_name
            and a.r_owner  = c1.owner
            and b.table_name = c1.table_name) loop
v_str:='alter table '||c2.owner||'.'||c2.table_name||
        ' enable constraint '
       	||c2.constraint_name;
--dbms_output.put_line(v_str);
execute immediate v_str; 
end loop;
exception
when others then 
dbms_output.put_line(c1.table_name||' '||sqlerrm);
end;
end loop;
end;
/
PL/SQL procedure successfully completed.


SQL> select constraint_name,status from dba_constraints
  2   where owner='JP' and table_name='EMPLOYEE';

CONSTRAINT_NAME                STATUS
------------------------------ --------
SYS_C0032318                   ENABLED
EMPLOYEE_FK                    ENABLED


SQL> select count(1) from jp.manager;

  COUNT(1)
----------
         1

SQL> select count(1) from jp.employee;

  COUNT(1)
----------
         2

Taking into consideration reader's views and requirements, I thought that I would revisit my article "FINDING FOREIGN KEY CONSTRAINTS" and fill in the gaps. Hence this article "FOREIGN KEY CONSTRAINTS REVISITED".

Reference:

Nulli Secundus
Finding Foreign Key Constraints in Oracle

JP Vijaykumar is an Oracle DBA. He loves logic, scripting, performance tuning and database architecture. In his leisure time he enjoys literature, carpentry and travel.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date