hi there,
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:
# ---------------------------------------------------------------------- #
# 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 help me getting this straigth (
and point me to my missconseptions)
thanks
robert
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
