DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Key violation error

  1. #1
    Join Date
    Feb 2005
    Posts
    55

    Key violation error

    I was trying to insert(append) some data using SQL into a table using access.
    The code I was using is:
    INSERT INTO FLIGHT(FlightNo, Capacity, DeptCode, ArrCode, DeptTime, ArrTime, WkSchedule)
    VALUES('1', 120, 'MEL', 'CBR', '1040', '1110', '12...67');

    There error comes from 'MEL' and 'CBR'

    I created the flight table using the following code:
    CREATE TABLE FLIGHT
    (FlightNo TEXT(10) PRIMARY KEY,
    Capacity NUMBER,
    DeptCode TEXT(3) REFERENCES CITY (CityCode),
    ArrCode TEXT(3) REFERENCES CITY (CityCode),
    DeptTime TEXT(4),
    ArrTime TEXT(4),
    WkSchedule TEXT(7),
    FOREIGN KEY (DeptCode, ArrCode) REFERENCES PRICES (DeptCode, ArrCode),
    FOREIGN KEY (DeptCode, ArrCode) REFERENCES DISTANCE (DeptCode, ArrCode));

    Price code
    CREATE TABLE PRICES
    (DeptCode TEXT(3) REFERENCES CITY (CityCode),
    ArrCode TEXT(3) REFERENCES CITY (CityCode),
    Price CURRENCY,
    PRIMARY KEY (DeptCode, ArrCode));

    Distance code
    CREATE TABLE DISTANCE
    (DeptCode TEXT(3) REFERENCES CITY (CityCode),
    ArrCode TEXT(3) REFERENCES CITY (CityCode),
    Distance NUMBER,
    PRIMARY KEY (DeptCode, ArrCode));


    When i delete the Foreign key code, i am able to insert the data.

    My question is, is their away for me to append to Flight by using the same code without having to delete the foreign key information.

    The foreign key has to be included as it is part of the schema i was given.

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Do you have MEL/CBR records in your Prices and Distance tables? The Foreign Key constraint will cause Inserts to fail unless related rows exist in the foreign tables.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Feb 2005
    Posts
    55
    Thanks. That helped me alot. I didnt have the correct records in the right table thankyou again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center