Two Foreign Keys?

[willot]

Its been MANY years since I messed with SQL and ive been messing about with it recently.

Using 3 tables im trying to make

[Volunteers] 1-Many> [lnktable]

[peterlaursen]

I think you should attach a structure-only dump of the tables involved.

Are you sure that datatypes are matching? Trying to create a FK on string columns with different collations … lots of possbilities!

[willot]

peterlaursen wrote on Sep 26 2007, 05:55 PM:

I think you should attach a structure-only dump of the tables involved.

Are you sure that datatypes are matching? Trying to create a FK on string columns with different collations … lots of possbilities!

heres the structure

/*Table structure for table `internalmessages` */

DROP TABLE IF EXISTS `internalmessages`;

CREATE TABLE `internalmessages` (

`IM_id` bigint(20) unsigned NOT NULL auto_increment,

`IM_date` datetime default NULL,

`IM_subject` varchar(15) character set utf8 default NULL,

`IM_message` varchar(255) character set utf8 default NULL,

`IM_isread` tinyint(1) default NULL,

PRIMARY KEY (`IM_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `linkimvol` */

DROP TABLE IF EXISTS `linkimvol`;

CREATE TABLE `linkimvol` (

`LNKIM_id` bigint(20) unsigned NOT NULL,

`LNKVOL_id` varchar(10) character set utf8 NOT NULL default '',

KEY `LNKIM_id` (`LNKIM_id`,`LNKVOL_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `volunteers` */

DROP TABLE IF EXISTS `volunteers`;

CREATE TABLE `volunteers` (

`VOL_id` varchar(10) character set utf8 NOT NULL,

`VOL_firstname` varchar(20) character set utf8 default NULL,

`VOL_surname` varchar(20) character set utf8 default NULL,

`VOL_streetnumbername` varchar(20) character set utf8 default NULL,

`VOL_suburb` varchar(20) character set utf8 default NULL,

`VOL_postcode` int(4) default NULL,

`VOL_phone` int(10) default NULL,

`VOL_admin` varchar(1) default NULL,

`VOL_shift` varchar(25) character set utf8 default NULL,

PRIMARY KEY (`VOL_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

[peterlaursen]

I think your initial post and the dump do not quite correspond. Table names are not the same! So i have a little trouble identifying exactly what the FK's should look like!

But I think the two *VOL_id's and the two *IM_id's ??

This

KEY `LNKIM_id` (`LNKIM_id`,`LNKVOL_id`)

will not work, I think. I think you will need a seperate key for every column like

KEY `LNKIM_id` (`LNKIM_id`),

KEY `LNKVOL_id` (`LNKVOL_id`),

but even after that change I too can only create ONE (and any) of the FK's

I am also wondering what I am missing ….

[willot]

peterlaursen wrote on Sep 26 2007, 09:32 PM:

I think your initial post and the dump do not quite correspond. Table names are not the same! So i have a little trouble identifying exactly what the FK's should look like!

Yep sorry the first post was just to give the indication of what I was doing. Ive also been messin with it since the first post

YeaH its confusing the heck outta me. I fugure it out eventally

[peterlaursen]

I think the reason simply is that the constraint name is allready in use! You can only use a constraint name once in a database.

SQLyog will 'propose' a constraint name generated from the 'child' table name. If you have more than one child row in a table you will need to enter a constraint name of your own choice.

[willot]

peterlaursen wrote on Sep 26 2007, 11:07 PM:

I think the reason simply is that the constraint name is allready in use! You can only use a constraint name once in a database.

SQLyog will 'propose' a constraint name generated from the 'child' table name. If you have more than one child row in a table you will need to enter a constraint name of your own choice.

nah tried that still doesnt want to know.

[Author]

Posts