Home » SQL & PL/SQL » SQL & PL/SQL » Errors on homework, need help (Oracle 9i)
icon5.gif  Errors on homework, need help [message #393427] Sun, 22 March 2009 15:18 Go to next message
gto400no1
Messages: 3
Registered: March 2009
Location: NJ
Junior Member
I have been trying to fix these errors for a few days now and just can't seem to figure out what I messed up on. Hopefully some fresh Eyes can help...

Quote:
drop table rep
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


drop table cus
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys



Table dropped.


Table dropped.


Table dropped.

create table rep (
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


create table cus (
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


then I am also geting an ORA-00001 unique constraint violated error.


heres my scipt if it helps
Quote:
clear screen

-- Drop Commands

drop table rep;
drop table cus;
drop table ord;
drop table ordln;
drop table part;

-- Create Commands

create table rep (
repno number(2),
last_name varchar2(10) constraint rep_last_name_nn not null,
first_name varchar2(10) constraint rep_first_name_nn not null,
street varchar2(14),
city varchar2(12),
state char(2),
zip char(5),
commision number(8,2),
rate number(3,2),
constraint rep_repno_pk primary key (repno));

create table cus (
cusno number(3),
cusname varchar(25) constraint cus_cusname_nn not null,
street varchar(14),
city varchar(12),
state char(2),
zip char(5),
Bal number(7,2),
climit number(7,2),
repno number(2),
constraint cus_cusno_pk primary key (cusno),
constraint cus_repno_fk foreign key (repno) references rep (repno));

create table ord (
ordno char(5),
orddate date,
cusno char(3),
constraint ord_ordno_orddate_pk primary key (ordno,orddate),
constraint ord_cusno_fk foreign key (cusno) references cus (cusno));

create table ordln (
ordno number(5),
partno char(4) constraint ordln_partno_nn not null,
noordered number(2),
qprice number(6,2),
constraint ordln_ornno_pk primary key (ordno));

create table part (
partno char(4),
description varchar(20) constraint part_description_nn not null,
onhand number(2),
class char(2),
warehouse number(1),
price number (6,2),
constraint part_partno_pk primary key (partno));

-- Insert the tables

insert into rep values ('20','Kaiser','Valerie','624 Randall','Grove','FL','3321',542.50,0.05);
insert into rep values ('35','Hull','Richard','532 Jackson','Sheldon','FL','33553',39216.00,0.07);
insert into rep values ('65','Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05);

insert into cus values ('148','Als Appliance and Sport','2837 Greenway','Fillmore','FL','33336',6550.00,7500.00,20);
insert into cus values ('282','Brookings Direct','3827 Devon','Grove','FL','33321',431.50,10000.00,35);
insert into cus values ('356','fergusons','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,65);
insert into cus values ('408','The Everything Shop','1828 Raven','Crystal','FL','33503',5285.25,5000.00,35);
insert into cus values ('462','Bargains Galore','3829 Central','Grove','FL','33321',3412.00,10000.00,65);
insert into cus values ('524','Klines','838 Ridgeland','Fillmore','FL','33336',12762.00,15000.00,20);
insert into cus values ('608','Johnsons Department Store','372 Oxford','Sheldon','FL','33553',2106.00,10000.00,65);
insert into cus values ('687','Lees Sport and Appliance','282 Evergreen','Altonville','FL','32543',2851.00,5000.00,35);
insert into cus values ('725','Deerfields Four Seasons','282 Columbia','Sheldon','FL','33553',248.00,7500.00,35);
insert into cus values ('842','All Season','28 Lakeview','Grove','FL','33321',8221.00,7500.00,20);

insert into ord values ('21608','20-Oct-2003','148');
insert into ord values ('21610','20-Oct-2003','356');
insert into ord values ('21613','21-Oct-2003','408');
insert into ord values ('21614','21-Oct-2003','282');
insert into ord values ('21617','23-Oct-2003','608');
insert into ord values ('21619','23-Oct-2003','148');
insert into ord values ('21623','23-Oct-2003','608');

insert into ordln values ('21608','AT94','11',21.95);
insert into ordln values ('01161','DR93','1',495.00);
insert into ordln values ('21610','DW11','1',399.99);
insert into ordln values ('21613','KL62','4',329.95);
insert into ordln values ('21614','KT03','2',595.00);
insert into ordln values ('02161','BV06','2',794.95);
insert into ordln values ('21617','CD52','4',150.00);
insert into ordln values ('21619','DR93','1',495.00);
insert into ordln values ('21623','KV29','2',1290.00);

insert into part values ('AT94','Iron','50','HW','3','24.95');
insert into part values ('BV06','Home Gym','45','SG','2','794.95');
insert into part values ('CD52','Microwave Oven','32','AP','1','165.00');
insert into part values ('DL71','Cordless Drill','21','HW','3','129.95');
insert into part values ('DR93','Gas Range','8','AP','2','495.00');
insert into part values ('DW11','Washer','12','AP','3','399.99');
insert into part values ('FD21','Stand Mixer','22','HW','3','159.95');
insert into part values ('KL62','Dryer','12','AP','1','349.95');
insert into part values ('KT03','Dishwasher','8','AP','3','595.00');
insert into part values ('KV29','Treadmill','9','SG','2','1390.00');

commit;


Thank you very much in advanced for any help that can be provided.
Re: Errors on homework, need help [message #393429 is a reply to message #393427] Sun, 22 March 2009 15:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
look into
Drop table table_name cascade constraints;
Re: Errors on homework, need help [message #393431 is a reply to message #393429] Sun, 22 March 2009 15:39 Go to previous messageGo to next message
gto400no1
Messages: 3
Registered: March 2009
Location: NJ
Junior Member
That was exactly it, thanks!
Re: Errors on homework, need help [message #393433 is a reply to message #393427] Sun, 22 March 2009 15:53 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Error messages book should be your first search:
ORA-02449: unique/primary keys in table referenced by foreign keys
 *Cause: An attempt was made to drop a table with unique or
         primary keys referenced by foreign keys in another table.
 *Action: Before performing the above operations the table, drop the
          foreign key constraints in other tables. You can see what
          constraints are referencing a table by issuing the following
          command:
          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

ORA-00001: unique constraint (%s.%s) violated
 *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
         For Trusted Oracle configured in DBMS MAC mode, you may see
         this message if a duplicate entry exists at a different level.
 *Action: Either remove the unique restriction or do not insert the key.

Then refer to SQL Reference.

Regards
Michel
Previous Topic: Deleting Duplicate Row Based on Timestamp
Next Topic: how can return value from column value more 3267k
Goto Forum:
  


Current Time: Wed Dec 07 15:01:23 CST 2016

Total time taken to generate the page: 0.08942 seconds