hi there,

I have a rather basic problem I would be glad to get some :
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:

# ---------------------------------------------------------------------- #
# Add table "tblUser" #
# ---------------------------------------------------------------------- #

CREATE TABLE tblUser
(
userDBID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(200)
)
ENGINE = InnoDB;

# ---------------------------------------------------------------------- #
# Add table "tblBuddies" #
# ---------------------------------------------------------------------- #

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');
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 me getting this straigth ( and point me to my missconseptions)

thanks
robert

Hi,

> I have a rather basic problem I would be glad to get some :
> 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 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

Martijn Tonies schrieb:
> Hi,
>
>> I have a rather basic problem I would be glad to get some :
>> 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?
>
yes! blunt stupidity!
(the insert was actually created programmatically, but I should have
seen it myself)
thanks
robert