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