Results 1 to 5 of 5

Thread: Unable to add records to a table containing a foreign key

  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unable to add records to a table containing a foreign key

    Hi,

    I am trying to create a database with three tables. One of the tables (Nutrition) is linked to the other (Candy Bars) with a foreign key. I can add records to two (Candy Bars and Stores) of the tables with no problems. When I try to add records to the table containing the foreign key, however, I get an error message. I have tried several ways to enter ways and I still get that error. Here is the code for the tables:


    Code:
    CREATE TABLE CandyBars
    (
    CandyBarID INTEGER CONSTRAINT CBarIDPk PRIMARY KEY,
    CandyName CHAR (30) NOT NULL,
    Manufacturer CHAR (30) NOT NULL,
    UnitsAvailable INTEGER,
    Description CHAR (50)
    );
    Code:
    CREATE TABLE Stores
    (
    StoreID INTEGER CONSTRAINT StoreIDPk PRIMARY KEY,
    StoreName CHAR (30) NOT NULL,
    OrderTotal CHAR (30) NOT NULL,
    Address CHAR (30) NOT NULL,
    Zip CHAR (30) NOT NULL,
    DeliveryMethod CHAR (30) NOT NULL
    );
    Code:
    CREATE TABLE Nutrition
    (
    NutritionID INTEGER CONSTRAINT NutrfID PRIMARY KEY,
    CandyBarID INTEGER NOT NULL,
    Calories CHAR (10) NOT NULL,
    TotalFat CHAR (10) NOT NULL,
    Sodium CHAR (10) NOT NULL,
    Carbs CHAR (10) NOT NULL,
    Protein CHAR (10) NOT NULL,
    CONSTRAINT CandyFk FOREIGN KEY (CandyBarID) REFERENCES CandyBars (CandyBarID) 
    ON UPDATE CASCADE 
    ON DELETE CASCADE
    );
    Here is a command that I tried to use to add data to the Nutrition table:

    Code:
    INSERT INTO Nutrition (NutritionID, Calories, TotalFat, Sodium, Carbs, Protein)
    VALUES (26, 75, 60, 1, 4, 12);
    That results in this error message:

    Microsoft Access can't append all of the records in the append query. Microsoft Access set 0 fields to Null due to type conversion failure, and it didn't add 0 records to the table due to key violations, 0 records due to lock violations, and 1 record due to validation rule violations.

    Please help.

    Ren

  2. #2
    Join Date
    Sep 2009
    Posts
    3
    Never mind, I figured out the problem and got things working.

  3. #3
    Join Date
    Mar 2006
    Location
    Oklahoma City, OK
    Posts
    184
    Was it due to this?

    Code:
    CandyBarID INTEGER NOT NULL
    Did you need to set a value in the fiuld CandyBarID?
    Boyd Trimmell aka HiTech Coach
    Microsoft MVP - Access Expert
    [SIGPIC][/SIGPIC]
    Office Programming 25+ years as a Software Developer specializing in:
    Business Process Management
    Accounting/Inventory Control
    Customer Relations Management (CRM)
    Electronic Data Interchange (EDI)

  4. #4
    Join Date
    Sep 2009
    Posts
    3
    Yep, that was the problem. I thought, since it was the foreign key, that it would be automatically populated with information. I didn't know that I had to enter it myself.

  5. #5
    Join Date
    Mar 2006
    Location
    Oklahoma City, OK
    Posts
    184
    If you are using a form/sub form, and have the Master/Child Linking field properties set,t hen you are tell Acess to handle the update.

    With a query, you have to do it manually.
    Boyd Trimmell aka HiTech Coach
    Microsoft MVP - Access Expert
    [SIGPIC][/SIGPIC]
    Office Programming 25+ years as a Software Developer specializing in:
    Business Process Management
    Accounting/Inventory Control
    Customer Relations Management (CRM)
    Electronic Data Interchange (EDI)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •