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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
