June 7th, 2008, 09:11 PM
|
|
Registered User
|
|
Join Date: Jun 2008
Posts: 1
Time spent in forums: 9 m 25 sec
Reputation Power: 0
|
|
|
Create A User Message Table
I need to create a "user message" table which stores messages sent between users in the system. The table will store who the message is sent to (msgTo) and who the message was sent from (msgFrom). I also need to create a foreign key to hold integrity because the msgTo and msgFrom is a specific user in the "users" table.
I'm trying to use a foreign key twice... The msgTo and msgFrom fields will both be a user id found on the "idusers" field.
Here is my table layout:
Code:
CREATE TABLE IF NOT EXISTS `v1_02`.`userMessages` (
`iduserMessages` INT NOT NULL ,
`msgTo` VARCHAR(45) NULL ,
`msgFrom` VARCHAR(45) NULL ,
`Topic` VARCHAR(45) NULL ,
`Message` BLOB NULL ,
`deleted` BOOLEAN NULL ,
`dateSent` DATETIME NULL ,
`dateRead` DATETIME NULL ,
`dateDeleted` DATETIME NULL ,
PRIMARY KEY (`iduserMessages`) ,
INDEX fk_userMessages_users (`msgTo` ASC) ,
INDEX fk_userMessages_users2 (`msgFrom` ASC) ,
CONSTRAINT `fk_userMessages_users`
FOREIGN KEY (`msgTo` )
REFERENCES `v1_02`.`users` (`idusers` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_userMessages_users2`
FOREIGN KEY (`msgFrom` )
REFERENCES `v1_02`.`users` (`idusers` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
I'm getting an error and I think it's because mysql won't allow me to create a references For instance, if I delete the foreign keys, it works just fine... But I want to keep the keys.
What do most people do when they want to create a message table? Can someone show me their schema?
|
Dev Articles Community Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
