List:General Discussion« Previous MessageNext Message »
From:robert rottermann Date:August 17 2007 11:59am
Subject:creating buddy list. request for help
View as plain text  
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

Thread
creating buddy list. request for helprobert rottermann17 Aug
  • Re: creating buddy list. request for helpMartijn Tonies17 Aug
    • Re: creating buddy list. request for helprobert rottermann17 Aug