-
Inserting data error!
Hi i've been working on this database problem for a while now. I thought i could figure it out myself but i am yet to do so. If anyone can help me with this it will be very much apprieciated! It seems to be a problem when i insert data into the JDcharges table. It keeps bring up an error and i can't quite work out why?
Here's the tables:
Code:
CREATE TABLE JDcustomers
(crefno NUMBER(4) NOT NULL,
cname VARCHAR2(20) NOT NULL,
cadd1 VARCHAR2(20) NOT NULL,
cdd2 VARCHAR2(20),
PRIMARY KEY (crefno));
CREATE TABLE JDvehicles
(regno VARCHAR2(7) NOT NULL,
v_crefno NUMBER(4) NOT NULL,
PRIMARY KEY (regno),
FOREIGN KEY(v_crefno) REFERENCES JDcustomers(crefno));
CREATE TABLE JDrepairs
(jobcode NUMBER(6) NOT NULL,
regno VARCHAR2(7) NOT NULL,
datein DATE NOT NULL,
finish DATE,
bayno NUMBER(2) NOT NULL,
PRIMARY KEY (jobcode),
FOREIGN KEY(regno) REFERENCES JDvehicles);
CREATE TABLE JDbays
(bayno NUMBER(2) NOT NULL,
cstatus VARCHAR2(1) NOT NULL,
PRIMARY KEY (bayno));
CREATE TABLE JDcharges
(jobcode NUMBER(6) NOT NULL,
itemcode NUMBER(4) NOT NULL,
PRIMARY KEY (itemcode),
FOREIGN KEY(jobcode) REFERENCES JDrepairs);
CREATE TABLE JDitems
(itemcode NUMBER(4) NOT NULL,
itemdesc VARCHAR2(20) NOT NULL,
cost VARCHAR2(20) NOT NULL,
PRIMARY KEY (itemcode));
And here is the data i want to enter, all of it works but the JDcharges one;
Code:
INSERT INTO JDcustomers
VALUES ('4444','INDUSTRIAL CLEANING','97 PARK ROAD','SOUTHAMPTON');
INSERT INTO JDcustomers
VALUES ('5555','FORK TRUCK SERVICES','28 CASTLE STREET','WINCHESTER');
INSERT INTO JDvehicles
VALUES ('HG07VEB',4444);
INSERT INTO JDvehicles
VALUES ('HB57DIW',4444);
INSERT INTO JDvehicles
VALUES ('HF57NAR',4444);
INSERT INTO JDvehicles
VALUES ('HF57RST',4444);
INSERT INTO JDvehicles
VALUES ('RK06TEC',5555);
INSERT INTO JDvehicles
VALUES ('RK56ADI',5555);
INSERT INTO JDvehicles
VALUES ('RN56UME',5555);
INSERT INTO JDvehicles
VALUES ('RD56SPE',5555);
INSERT INTO JDrepairs
VALUES (139087,'RK06TEC','03-APR-08','06-APR-08',15);
INSERT INTO JDrepairs
VALUES (139587,'HG07VEB','10-APR-08','11-APR-08',11);
INSERT INTO JDrepairs
VALUES (140187,'HB57DIW','13-APR-08','',04);
INSERT INTO JDrepairs
VALUES (139987,'RK56ADI','05-MAY-08','10-MAY-08',12);
INSERT INTO JDrepairs
VALUES (140387,'HF57NAR','16-MAY-08','',15);
INSERT INTO JDrepairs
VALUES (140087,'RN56UME','09-MAY-08','14-MAY-08',01);
INSERT INTO JDrepairs
VALUES (140287,'RD56SPE','02-APR-08','03-APR-08',11);
INSERT INTO JDbays
VALUES (01,'E');
INSERT INTO JDbays
VALUES (04,'U');
INSERT INTO JDbays
VALUES (05,'E');
INSERT INTO JDbays
VALUES (11,'E');
INSERT INTO JDbays
VALUES (12,'E');
INSERT INTO JDbays
VALUES (15,'U');
INSERT INTO JDcharges
VALUES (139087,1470);
INSERT INTO JDcharges
VALUES (139087,6281);
INSERT INTO JDcharges
VALUES (139087,5580);
INSERT INTO JDcharges
VALUES (139587,4609);
INSERT INTO JDcharges
VALUES (139587,1470);
INSERT INTO JDcharges
VALUES (139587,6281);
INSERT INTO JDcharges
VALUES (140287,1470);
INSERT INTO JDcharges
VALUES (140287,6281);
INSERT INTO JDcharges
VALUES (139987,5580);
INSERT INTO JDcharges
VALUES (139987,4609);
INSERT INTO JDitems
VALUES (1470,'OIL CHANGE',7.30);
INSERT INTO JDitems
VALUES (6281,'WINDSCREEN WIPER',12.70);
INSERT INTO JDitems
VALUES (5580,'LIGHT BULB',2.95);
INSERT INTO JDitems
VALUES (4609,'RESPARY MATERIALS',95.64);
The first 4 rows of data is entered, then this is the error code it gives me:
Code:
INSERT INTO JDcharges
*
ERROR at line 1:
ORA-00001: unique constraint (DREWJ91.SYS_C00978008) violated
Can anyone tell me where i'm going wrong please?
-
you are inserting the same value twice
Code:
INSERT INTO JDcharges
VALUES (139087,1470);
INSERT INTO JDcharges
VALUES (139587,1470);
violating the unique constraint, kinda like it tells you
-
ah it might be because its the primary key! But the data that needs to be entered isnt unique, i need an auto number primary key i guess?
Last edited by Drewery86; 03-27-2008 at 04:15 PM.
-
I think i've got it, but how do you make a primary key with two columns?
-
I've done it! You can delete this thread now lol sorry
-
we are so proud of you son you have learned how to fish.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|