Monday, December 17, 2012

On ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Few days ago I received an email from a user who had two empty table, one referenced by the other. He was not able to execute a truncate command on the parent table and asked me to solve his error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys.

Truncate command is very useful when you want to completely remove all data from a table: for very large tables it is usually the best way to remove your data.
When you use the TRUNCATE command Oracle sets back to zero the HWM (high-water mark) of the table and, compared with the DELETE command, less UNDO and REDO information is generated.
You can query the dba_extents to see how many extents are allocated for your table.

However using TRUNCATE command has some "disadvantages": first you have to remember it's a DDL command so it will commit automatically any pending transactions. Another one is that you can receive the following error when the table you are truncating is referenced by an "enabled" foreign key: ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
To successfully complete your TRUNCATE command you have to temporarily disable the foreign key.

Let's see it with an example.
Create two tables: one table (hr.parent) has one primary key referenced by a second table (hr.child) using a constraint.
SQL> create table hr.parent (a number);

Table created.

SQL> create table hr.child (b number, a_ref number);

Table created.

SQL> create index parent_pk on hr.parent(a);

Index created.

SQL> alter table hr.parent add constraint parent_pk primary key (a);

Table altered.

SQL> alter table hr.child add constraint child_fk foreign key (a_ref) references hr.parent (a);

Table altered.
No extent is allocated for PARENT table. I'm working with Oracle Database 11G R2, so have a look at deferred_segment_creation initialization parameter to justify why you have zero extent allocated (or read this link)
SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         0
And no rows still are created.
SQL> select * from hr.parent;

no rows selected

SQL> select count(*) from hr.child;

  COUNT(*)
----------
         0
In this situation we can truncate our PARENT table with any problem.
SQL> truncate table hr.parent;

Table truncated.
But what does it happen when records are inserted into it ? Let's populate PARENT table with some data:
SQL> begin
  2  for  indx IN 1 .. 10000
  3  loop
  4      execute immediate 'insert into hr.parent values (:1)' using indx;
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.
PARENT table is now formed by 3 extents.
SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3
As you can see we are no more allowed to truncate PARENT table even if any data on CHILD table are referencing to it.
SQL>  truncate table hr.parent;
 truncate table hr.parent
                   *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> select count(*) from hr.child;

  COUNT(*)
----------
         0
To successfully complete the truncate command we have to disable the foreign key...
SQL> alter table hr.child modify constraint child_fk disable;

Table altered.
... execute the truncate command...
SQL>  truncate table hr.parent;

Table truncated.
.. and enable again the foreign key.
SQL> alter table hr.child modify constraint child_fk enable;

Table altered.
PARENT table is now formed by one extent.
SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1
Let's populate again PARENT table with 10000 records:
SQL> begin
  2  for  indx IN 1 .. 10000
  3  loop
  4      execute immediate 'insert into hr.parent values (:1)' using indx;
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.
PARENT table is formed by 3 extents.
SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3
Let's see what it happens to the HWM using delete command.
SQL> delete from hr.parent;

10000 rows deleted.

SQL> commit;

Commit complete.
PARENT table is always formed by 3 extents.
SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         3
It's still not possible to truncate PARENT table...
SQL> truncate table hr.parent;
truncate table hr.parent
                  *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
... even when no rows are available and referenced.
SQL> select count(*) from hr.parent;

  COUNT(*)
----------
         0

SQL> select count(*) from hr.child;

  COUNT(*)
----------
         0
You can truncate PARENT table again only if foreign key is disabled
SQL> alter table hr.child modify constraint child_fk disable;

Table altered.

SQL> truncate table hr.parent;

Table truncated.
PARENT table is now formed by 1 extents.
SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1
If you want to see the extent allocated as soon as you create PARENT table, you have to use the "segment creation immediate" option on your create table statement.
SQL> drop table hr.child; 

Table dropped.

SQL> drop table hr.parent;

Table dropped.

SQL> create table hr.parent (a number) segment creation immediate;

Table created.

SQL> select count(*) from dba_extents
  2  where segment_name = 'PARENT';

  COUNT(*)
----------
         1
That's all.

13 comments:

Anonymous said...

gcghdl http://pa-louisvuittoncheap.com bxbusg http://www.mccanadagooseoutlet.com dkuvnr http://www.sale-cheapmontblanc.com szwkqu http://cheaperbeatssbydre.com fwndly http://palouisvuittonoutlet.com ckqumz [url=http://pa-louisvuittoncheap.com]louis vuitton sale[/url] ltyvt [url=http://www.mccanadagooseoutlet.com]canada goose outlet[/url] vbazu [url=http://www.sale-cheapmontblanc.com]mont blanc[/url] casis [url=http://cheaperbeatssbydre.com]dr dre beats[/url] ylisdk [url=http://palouisvuittonoutlet.com]louis vuitton sale[/url] srju

Anonymous said...

qitoca http://www.capemaycitypolice.com/MonstBeatsByDre.html quekqj http://www.capemaycitypolice.com/cheapnikeairmaxb.html chncbi http://www.chancemccann.com/cheap-airjordans.html kyilto http://www.oceancityseafood.com/celinehandbags.html pseflt http://maryhelverson.com/celinehandbagsb.html woqqtt [url=http://www.capemaycitypolice.com/MonstBeatsByDre.html]dr dre beats[/url] sgywh [url=http://www.capemaycitypolice.com/cheapnikeairmaxb.html]nike air max[/url] rqezi [url=http://www.chancemccann.com/cheap-airjordans.html]cheap jordans[/url] emnkl [url=http://www.oceancityseafood.com/celinehandbags.html]celine bag[/url] layafa [url=http://capeMayLinen.com/cheapnikeshoes.html]air max[/url] bypp

Anonymous said...

ykcisp http://www.louisvuittonuk-bags.co.uk aenmwe [url=http://www.louisvuittonuk-bags.co.uk]louisvuittonuk-bags.co.uk[/url] syiovx [url=http://www.louisvuittonuk-bags.co.uk]http://www.louisvuittonuk-bags.co.uk[/url] qmclmv [url=http://www.louisvuittonuk-bags.co.uk]louis vuitton uk[/url] rqubuc [url=http://www.louisvuittonuk-bags.co.uk]louis vuitton uk[/url] ovuzti [url=http://www.louisvuittonuk-bags.co.uk]louis vuitton handbags[/url] prnoav

Anonymous said...

After going over a number of the blog posts on your blog, I seriously appreciate your way
of writing a blog. I added it to my bookmark site list and will be checking back soon.
Please visit my website too and tell me how you feel.


Also visit my blog post: Louis Vuitton Discount

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose