Inserting data error!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Inserting data error!

  1. #1
    Join Date
    Mar 2008
    Posts
    7

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  3. #3
    Join Date
    Mar 2008
    Posts
    7
    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 05:15 PM.

  4. #4
    Join Date
    Mar 2008
    Posts
    7
    I think i've got it, but how do you make a primary key with two columns?

  5. #5
    Join Date
    Mar 2008
    Posts
    7
    I've done it! You can delete this thread now lol sorry

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  


Click Here to Expand Forum to Full Width