Another Problem

[ThePsion5]

Hi again. I created a table in MySQL using the following statements:

CREATE TABLE ClientsToKeywords(ClientToKeywordID INTEGER NOT NULL AUTO_INCREMENT,ClientID INTEGER NOT NULL,KeywordID INTEGER NOT NULL,Rating INTEGER,CONSTRAINT PRIMARY KEY (ClientToKeywordID),CONSTRAINT FOREIGN KEY (ClientID) REFERENCES Clients(ClientID),CONSTRAINT FOREIGN KEY (KeywordID) REFERENES Keywords (KeywordID),CONSTRAINT UNIQUE (ClientID, KeywordID),) AUTO_INCREMENT = 0;

During the process of populating the database with some test data, i try and enter any one of these statement:

INSERT INTO ClientsToKeywords VALUES(0, 0, 2, 4);INSERT INTO ClientsToKeywords VALUES(0, 0, 5, 4);INSERT INTO ClientsToKeywords VALUES(0, 0, 0, 4);

And recieve this error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('test3/clientstokeywords', CONSTRAINT FOREIGN KEY ('ClientID') REFERENCES 'clients' ('ClientID'))

Unfortunately, I'm new to databases, and I don't understand why this would happen. Any help?

[aspnetguy]

first off if ClientToKeywordID is the PK then you can only have one ClientToKeywordID with the value of 0.What the error is saying is that since ClientID is a FK to Clients > ClientID (you are trying to insert a value of 0 for ClientID) there must be a record in Clients with the value of ClientID equal to 0...the error is saying you do not have such a record.By making ClientID a FK to Clients > ClientID you cannot place any ClientID in ClientToKeywordID that does not exist in Clients > ClientID.I hope I explained that well.

[ThePsion5]

first off if ClientToKeywordID is the PK then you can only have one ClientToKeywordID with the value of 0.What the error is saying is that since ClientID is a FK to Clients > ClientID (you are trying to insert a value of 0 for ClientID) there must be a record in Clients with the value of ClientID equal to 0...the error is saying you do not have such a record.By making ClientID a FK to Clients > ClientID you cannot place any ClientID in ClientToKeywordID that does not exist in Clients > ClientID.I hope I explained that well.

<{POST_SNAPBACK}>

The ClientID is set to auto-increment if a value of 0 or null is entered, and previous to that I had inserted values into both the Client and Keyword tables, so I don't see how this is a problem...Would it help if I told you that I was using MySQL version 5.0.16?

[aspnetguy]

not really but it would help if you posted the structure and full contents of the Clients and ClientsToKeyboards tables.

[ThePsion5]

not really but it would help if you posted the structure and full contents of the Clients and ClientsToKeyboards tables.

<{POST_SNAPBACK}>

Here's the exact syntax I used to create the database:

CREATE DATABASE ESiteTest3;USE ESiteTest3;CREATE TABLE Clients(ClientID INTEGER NOT NULL AUTO_INCREMENT,SugarID CHAR(36),ClientName CHAR(60) NOT NULL,ClientWebsite CHAR(60) NOT NULL,NumKeywords INTEGER DEFAULT 0,CONSTRAINT Client_PK PRIMARY KEY (ClientID),CONSTRAINT ClientName_Unique UNIQUE (ClientID),CONSTRAINT ClienWeb_Unique UNIQUE (ClientWebsite)) AUTO_INCREMENT = 0;CREATE TABLE Keywords(KeywordID INTEGER NOT NULL AUTO_INCREMENT,Keyword CHAR(60) NOT NULL,NumClients INTEGER DEFAULT 0,CONSTRAINT Keyword_PK PRIMARY KEY (KeywordID),CONSTRAINT Keyword_Unique UNIQUE (KeywordID, Keyword)) AUTO_INCREMENT = 0;CREATE TABLE ClientsToKeywords(ClientToKeywordID INTEGER NOT NULL AUTO_INCREMENT,ClientID INTEGER NOT NULL,KeywordID INTEGER NOT NULL,Rating INTEGER,CONSTRAINT ClientToKeyword_PK PRIMARY KEY (ClientToKeywordID),CONSTRAINT ClientID_FK FOREIGN KEY (ClientID) REFERENCES Clients(ClientID),CONSTRAINT KeywordID_FK FOREIGN KEY (KeywordID) REFERENCES Keywords(KeywordID),CONSTRAINT ClientToKeywordID_Unique UNIQUE (ClientToKeywordID),CONSTRAINT ClientToKeywordRelationship_Unique UNIQUE (ClientID, KeywordID),CONSTRAINT ClientToKeywordRating_Check CHECK (rating >=0 AND rating <= 5)) AUTO_INCREMENT = 0;

And I use this code to populate it with test data:

INSERT INTO Clients VALUES(0, "", "El Doomo Industries", "www.eldoomo.com", 0);INSERT INTO Clients VALUES(0, "", "Fartknocker Inc.", "www.######.com", 0);INSERT INTO Clients VALUES(0, "", "Alien Empire", "www.aempire.net", 0);INSERT INTO Clients VALUES(0, "", "Redemption!", "www.redemption.com1", 0);INSERT INTO Clients VALUES(0, "", "The Sandwitchery", "www.sandwitchery.com", 0);INSERT INTO Clients VALUES(0, "", "Cornfries for the Heart", "www.ctfh.com", 0);INSERT INTO Clients VALUES(0, "", "Happytimes Personal Counseling", "www.happytimes.org", 0);INSERT INTO Clients VALUES(0, "", "Alliance Software", "www.alliance.com", 0);INSERT INTO Clients VALUES(0, "", "Ninja Inc.", "www.ninja.com", 0);INSERT INTO Keywords VALUES(0, "Doom", 0);INSERT INTO Keywords VALUES(0, "Unpleasant", 0);INSERT INTO Keywords VALUES(0, "Evil", 0);INSERT INTO Keywords VALUES(0, "Foriegners", 0);INSERT INTO Keywords VALUES(0, "Silence", 0);INSERT INTO Keywords VALUES(0, "Video Games", 0);INSERT INTO Keywords VALUES(0, "Food", 0);INSERT INTO Keywords VALUES(0, "Happiness", 0);INSERT INTO Keywords VALUES(0, "Coolness", 0);INSERT INTO ClientsToKeywords VALUES(0, 0, 0, 4);INSERT INTO ClientsToKeywords VALUES(0, 1, 0, 4);INSERT INTO ClientsToKeywords VALUES(0, 3, 0, 4);INSERT INTO ClientsToKeywords VALUES(0, 5, 0, 4);INSERT INTO ClientsToKeywords VALUES(0, 8, 0, 4);INSERT INTO ClientsToKeywords VALUES(0, 3, 1, 4);INSERT INTO ClientsToKeywords VALUES(0, 5, 1, 4);INSERT INTO ClientsToKeywords VALUES(0, 2, 1, 4);INSERT INTO ClientsToKeywords VALUES(0, 1, 1, 4);INSERT INTO ClientsToKeywords VALUES(0, 0, 2, 4);INSERT INTO ClientsToKeywords VALUES(0, 2, 2, 4);INSERT INTO ClientsToKeywords VALUES(0, 5, 2, 4);INSERT INTO ClientsToKeywords VALUES(0, 2, 3, 4);INSERT INTO ClientsToKeywords VALUES(0, 8, 3, 4);INSERT INTO ClientsToKeywords VALUES(0, 5, 4, 4);INSERT INTO ClientsToKeywords VALUES(0, 6, 4, 4);INSERT INTO ClientsToKeywords VALUES(0, 8, 4, 4);INSERT INTO ClientsToKeywords VALUES(0, 0, 5, 4);INSERT INTO ClientsToKeywords VALUES(0, 7, 5, 4);INSERT INTO ClientsToKeywords VALUES(0, 8, 5, 4);INSERT INTO ClientsToKeywords VALUES(0, 4, 6, 4);INSERT INTO ClientsToKeywords VALUES(0, 5, 6, 4);INSERT INTO ClientsToKeywords VALUES(0, 3, 7, 4);INSERT INTO ClientsToKeywords VALUES(0, 7, 7, 4);INSERT INTO ClientsToKeywords VALUES(0, 7, 8, 4);INSERT INTO ClientsToKeywords VALUES(0, 8, 8, 4);

I hope this helps!

[aspnetguy]

it boils down to you cannot insert 0 or null into ClientID of the ClientsToKeyboards table because there is not record in Clients where ClientID equals 0 or null because it ClientID of Clients is set to auto increment.If ClientID was not a FK of ClientsToKeyboards it would work but since it is a FK it will not.We can discuss this all day but those are the facts, you cannot do it.

[ThePsion5]

it boils down to you cannot insert 0 or null into ClientID of the ClientsToKeyboards table because there is not record in Clients where ClientID equals 0 or null because it ClientID of Clients is set to auto increment.If ClientID was not a FK of ClientsToKeyboards it would work but since it is a FK it will not.We can discuss this all day but those are the facts, you cannot do it.

<{POST_SNAPBACK}>

Ah, I think I see what the problem is...when I set the auto-increment value to 0 the first value actually inserted into the ClientID in the Clients table was 1, not 0 as I thought. This should clear everything up.Thanks for the help

[aspnetguy]

yes that should clear everything up....lol....good luck.