Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449:
SQL> drop table agreement;
drop table agreement
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this problem, and from which tables.
Luckily, there is a quick workaround to ORA-02449, provided you are ready to heed the risks of the approach along with it.
Continue Reading …
A quick tip to get rid of the plodding way of inserting data into a table with multiple INSERT statements. Know what I mean? Say you have a table COLORS with this structure:
Name Type
---------------- ------------
NAME VARCHAR2(30)
CATEGORY VARCHAR2(10)
And you want to create this data in the table COLORS:
NAME CATEGORY
------------------------------ --------
yellow 1
red 1
blue 1
yellow 2
blue 2
Hands up all who write insert statements this way:
insert into colors (name, category)
values ('yellow', 1);
insert into colors (name, category)
values ('red', 1);
insert into colors (name, category)
values ('blue', 1);
insert into colors (name, category)
values ('yellow', 2);
insert into colors (name, category)
values ('blue', 2);
Continue Reading …
