SEO

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

roohbir wrote:
> 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
>

You already have a constraint named 'ContactId'. Constraint names, if
supplied, must be unique.

I usually make my constraint names based on both table names (and a
column name if necessary).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================