From: Martijn Tonies
Date: August 17 2007 12:13pm
Subject: Re: creating buddy list. request for help
List-Archive: http://lists.mysql.com/mysql/208614
Message-Id: <018101c7e0c8$1536c360$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-15"
Content-Transfer-Encoding: 7bit

Hi,

> I have a rather basic problem I would be glad to get some help:
> What I intend to do is:
> - create a person-list
> - create a buddy-list
>     each entry in the person-list can have 0 to many buddies
> - when an entry in the person-list is deleted, I would like to
>   have correspnding entries in the buddies list removed.
>
> This are my tables:
> CREATE TABLE  tblUser
> (
>   userDBID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
>   username VARCHAR(50) NOT NULL UNIQUE,
>   description VARCHAR(200)
> )
> ENGINE = InnoDB;
>
> CREATE TABLE tblBuddies (
>     userDBID INTEGER,
>     buddyDBID INTEGER,
>     CONSTRAINT PK_tblBuddies PRIMARY KEY (userDBID, buddyDBID)
> )
> ENGINE = InnoDB;
> CREATE INDEX userDBID_buddyDBID ON tblBuddies (userDBID, buddyDBID);
> ALTER TABLE tblBuddies ADD CONSTRAINT tblBuddies_parent
>     FOREIGN KEY (userDBID) REFERENCES tblUser (userDBID);
>
> ALTER TABLE tblBuddies ADD CONSTRAINT tblBuddies_buddy
>     FOREIGN KEY (buddyDBID) REFERENCES tblUser (userDBID) ON DELETE
CASCADE;
>
> When I execute the following statement:
>     INSERT INTO tblBuddies (userDBID,buddyDBID) VALUES ('test user', 'test
user 2');

You are inserting string values, not ID (integer) values. Any reason for
that?

> I get the the error:
>     ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails
> (`adhoco_vpn_mapper_test/tblBuddies`, CONSTRAINT `tblBuddies_parent`
FOREIGN KEY (`userDBID`) REFERENCES `tblUser`
> (`userDBID`))
>
> I would be very gratefull, if somebody could help me getting this straigth
( and point me to my missconseptions)

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com