Foreign keys

[dany]

I cannot create foreign keys on InnoDB tables (Windows 2000/MySQL 4.0.9).

I'm trying to create a foreign key between Owners.IDcar and Car.ID but i think that Webyog 3.01 understands that I'm trying to do something different (in the SQL statement it writes IDcar two times).

Here are the SQL statements:

First table

Code:

create table `test`.`Cars` (
 `ID` bigint   NOT NULL ,
`name` char (20)   NOT NULL  ,
PRIMARY KEY ( `ID` )
)type=InnoDB row_format=dynamic

Second table

Code:

create table `test`.`Owners` (
 `ID` bigint   NOT NULL ,
`name` char (15)   NOT NULL ,
`IDcar` bigint   NULL  ,
PRIMARY KEY ( `ID` )
)type=InnoDB row_format=dynamic

Indexes

Code:

alter table `test`.`owners` add index `indexIDcar` ( IDcar )
alter table `test`.`owners` add foreign key (IDcar) references `cars` (IDcar) on delete restrict

Thanks

[CalEvans]

Quote:

i think that Webyog 3.01 understands that I'm trying to do something different (in the SQL statement it writes IDcar two times).

Danny,

Can you be more specific?

1: Are you trying to use the 'Manage Relationships' feature or are you writing the sql yourself and executing it in the command window?

2: If you are using 'Manage Relationships' which table did you select before pressing the button? (Cars or Owners?)

3: What relevant output are you getting? (check your mysql.err file, check you mysql.log file, check the Messages window of SQLyog)

4: Can you post the output you described above where you get IDCar twice?

Your code looks solid (without having actually executed it myself) Other than:

Quote:

alter table `test`.`owners` add foreign key (IDcar) references `cars` (IDcar) on delete restrict

looks like it should be

Quote:

alter table `test`.`owners` add foreign key (IDcar) references `cars` (ID) on delete restrict

I've used the Manage Relationships once and while it took a couple of tries to get it right (mainly my misunderstandings of how InnoDB worked) I was able to create the relationship I needed.

HTH,

=C=

[CalEvans]

Code:

create table `test`.`Cars` (
`ID` bigint   NOT NULL ,
`name` char (20)   NOT NULL  ,
PRIMARY KEY ( `ID` )
)type=InnoDB row_format=dynamic;

create table `test`.`Owners` (
`ID` bigint   NOT NULL ,
`name` char (15)   NOT NULL ,
`IDcar` bigint   NULL  ,
PRIMARY KEY ( `ID` )
)type=InnoDB row_format=dynamic;

alter table `test`.`Owners` add index `indexIDcar` ( IDcar );
alter table `test`.`Owners` add foreign key (IDcar) references `Cars` (ID) on delete restrict;

Ok, I finally broke down and executed the code above (cut form your first post and pasted into the command window of SQLyog.

I get:

Code:

CREATE TABLE `Cars` (                                                                                                                                        
        `ID` bigint(20) NOT NULL default '0',                                                                                                                        
        `name` varchar(20) NOT NULL default '',                                                                                                                      
        PRIMARY KEY  (`ID`)                                                                                                                                          
        TYPE=InnoDB ROW_FORMAT=DYNAMIC                                                                                                                              

and

Code:

CREATE TABLE `Owners` (                                                                                                                                                                                                                                                                
        `ID` bigint(20) NOT NULL default '0',                                                                                                                                                                                                                                                  
        `name` varchar(15) NOT NULL default '',                                                                                                                                                                                                                                                
        `IDcar` bigint(20) default NULL,                                                                                                                                                                                                                                                      
        PRIMARY KEY  (`ID`),                                                                                                                                                                                                                                                                  
        KEY `indexIDcar` (`IDcar`),                                                                                                                                                                                                                                                            
        FOREIGN KEY (`IDcar`) REFERENCES `test.Cars` (`ID`)                                                                                                                                                                                                                                    
        TYPE=InnoDB ROW_FORMAT=DYNAMIC

create table `test`.`Owners` (
`ID` bigint   NOT NULL ,
`name` char (15)   NOT NULL ,
`IDcar` bigint   NULL  ,
PRIMARY KEY ( `ID` )
)type=InnoDB row_format=dynamic;

alter table `test`.`Owners` add index `indexIDcar` ( IDcar );
alter table `test`.`Owners` add foreign key (IDcar) references `Cars` (ID) on delete restrict;
 

as the output of double clicking on my new tables in test. As you can see, the FK relationship exists in owners.

The only 2 things I had to do were

1: Change the last alter table to:

Code:

alter table `test`.`Owners` add foreign key (IDcar) references `Cars` (ID) on delete restrict;

As I pointed out before, you were trying to reference cars.IDCar and that field does not exist. I changed it to Cars.ID and it works fine.

2: Table names in MySQL are case sensitive. Since you created them with proper capitalization you HAVE to use that everywhere.

HOWEVER I used the Manage Relations tool to delete the relationship and try and re-create it and it does not work. (Ritesh, we do have a problem here). I've submitted thisas a bug.

[dany]

Quote:

Your code looks solid (without having actually executed it myself) Other than:

alter table `test`.`owners` add foreign key (IDcar) references `cars` (IDcar) on delete restrict

looks like it should be

alter table `test`.`owners` add foreign key (IDcar) references `cars` (ID) on delete restrict

All the SQL code shown above was created by SQLyog 3.01 .

I've tried to create the foreign key using the new feature “Manage relationships“.

The table that was selected before using Manage relationships was Owners.

I want to specify that I'm able to create a foreign key between Oweners.ID and Cars.ID but this is not what I want!

Thanks

[dany]

Quote:

you were trying to reference cars.IDCar and that field does not exist

Hi CalEvans,

I want you to understand that the error is not mine, the wrong code was created by SQLyog.

Bye

[Ritesh]

Our developers has confirmed it as bug.

It happens only when the Source Columna and the Reference Column have different name.

We will be releasing SQLyog 3.02 withing 48 hrs which will have this bug fixed. Sorry for the inconvenience caused and thanks for your patience.

[Author]

Posts