Hi,

I have a database which has many tables, but my question relates to
these 4 tables: USER, CONTACT, PHONE, EVENT.

USER has a 1 to many relationship with CONTACT. CONTACT in turn has 1
to many relationship with 2 tables PHONE and EVENT. PK of CONTACT is
supposed to be the FK of both EVENT and PHONE. I can create the
relationship successfully between CONTACT and either one of these
tables but not both. If I make the relationship between, say PHONE,
then I cannot create the FK in EVENT and vice-versa.
I get Error 1005 and 150. I have also pasted the error report at the
end.

I am new to databases so it might be some fundamental database design
issue but I can't figure it. Would be really grateful if somebody
could explain it to me.

The SQL scripts are pasted below:

DROP TABLE IF EXISTS `my_address_book`.`user`;
CREATE TABLE `my_address_book`.`user` (
`UserId` int(10) unsigned NOT NULL auto_increment,
`FirstName` varchar(45) NOT NULL,
`LastName` varchar(45) NOT NULL,
PRIMARY KEY (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `my_address_book`.`contact`;
CREATE TABLE `my_address_book`.`contact` (
`ContactId` int(10) unsigned NOT NULL auto_increment,
`UserId` int(10) unsigned NOT NULL,
`Title` varchar(45) NOT NULL,
`FirstName` varchar(45) NOT NULL,
`LastName` varchar(45) NOT NULL,
PRIMARY KEY (`ContactId`),
KEY `UserId` (`UserId`),
CONSTRAINT `UserId` FOREIGN KEY (`UserId`) REFERENCES `user`
(`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `my_address_book`.`phone`;
CREATE TABLE `my_address_book`.`phone` (
`PhoneId` int(10) unsigned NOT NULL auto_increment,
`ContactId` int(10) unsigned NOT NULL,
`PhoneNo` int(10) unsigned NOT NULL,
`PhoneExt` int(10) unsigned NOT NULL,
`PhoneTypeId` int(10) unsigned NOT NULL,
PRIMARY KEY (`PhoneId`),
KEY `ContactId` (`ContactId`),
KEY `PhoneTypeId` (`PhoneTypeId`),
CONSTRAINT `PhoneTypeId` FOREIGN KEY (`PhoneTypeId`) REFERENCES
`phone_type` (`PhoneTypeId`),
CONSTRAINT `ContactId` FOREIGN KEY (`ContactId`) REFERENCES `contact`
(`ContactId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `my_address_book`.`event`;
CREATE TABLE `my_address_book`.`event` (
`EventId` int(10) unsigned NOT NULL auto_increment,
`ContactId` int(10) unsigned NOT NULL,
`EventTypeId` int(10) unsigned NOT NULL,
`EventDate` datetime NOT NULL,
`EventName` varchar(45) NOT NULL,
`EventPlace` varchar(45) NOT NULL,
PRIMARY KEY (`EventId`),
KEY `EventTypeId` (`EventTypeId`),
CONSTRAINT `EventTypeId` FOREIGN KEY (`EventTypeId`) REFERENCES
`event_type` (`EventTypeId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

In this EVENT table when I try to make 'ContactId' a FK, I get the
following error:
Error while executing query

ALTER TABLE `my_address_book`.`event`
ADD CONSTRAINT `ContactId`
FOREIGN KEY `ContactId`(ContactId`)
REFERENCES `contact`(`ContactId`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

MySQL Error Number 1005
Can't create table'.\my_address_book\#sql-b10_1.frm'(errno:121)


THANKS in advance.
Ros