Unsupported Screen Size: The viewport size is too small for the theme to render properly.

managing relationships with innodb

forums › forums › SQLyog › Using SQLyog › managing relationships with innodb

  • This topic is empty.
Viewing 3 reply threads
  • Author
    Posts
    • April 16, 2003 at 8:24 pm #7843
      mchamber
      Member

      Who can help?

      I have just installed mysql 4.xxx and I would like

      to use the flexibility of a relational database.

      I understand that I need to make all my tables InnoDB

      format which I have…

      when I try to set up the relationships however, using

      SQLyog, it shows me an error with about 5 possible issues

      I don't have the first clue what is wrong (newbie) so I am

      hoping someone does.

      Thanks

      Mick

    • April 17, 2003 at 8:56 am #14088
      Ritesh
      Member

      Hello

      1.) Make sure that both the tables are InnoDB. You can execute

      show table status from 'dbname' to know that.

      2.) You have proper indexes on the columns which will participate in the FK Relationship.

      It will be very helpful if you could post the Create Table statement… of both the tables and the SQL generated by

      SQLyog for creating the relationship. You can get the SQL from the History Tab.

      HTH

    • April 17, 2003 at 6:26 pm #14089
      mchamber
      Member

      What do you think?

      Table 1

      [11:14:45 AM][ 0 ms] show databases

      [11:14:47 AM][1232 ms] show table status from `riley`

      [11:14:49 AM][ 0 ms] show table status from `riley`

      [11:14:49 AM][ 20 ms] use `riley`

      [11:14:50 AM][ 180 ms] show fields from `riley`.`account`

      [11:14:50 AM][ 0 ms] show keys from `riley`.`account`

      [11:14:50 AM][ 130 ms] show create table `riley`.`account`

      [11:15:00 AM][ 20 ms] describe `riley`.`account`

      [11:15:00 AM][ 0 ms] show index from `riley`.`account`

      [11:15:05 AM][ 80 ms] use `account`

      [11:17:08 AM][ 0 ms] show fields from `riley`.`account`

      [11:17:08 AM][ 0 ms] show keys from `riley`.`account`

      [11:17:08 AM][ 10 ms] show create table `riley`.`account`

      [11:17:26 AM][ 10 ms] show fields from `riley`.`tams`

      [11:17:26 AM][ 0 ms] show keys from `riley`.`tams`

      [11:17:26 AM][ 0 ms] show create table `riley`.`tams`

      [11:17:26 AM][ 0 ms] describe `riley`.`tams`

      [11:17:26 AM][ 0 ms] show index from `riley`.`tams`

      [11:17:46 AM][ 0 ms] show table status from `riley`

      [11:19:10 AM][ 60 ms] create table `riley`.`TAMS` (

      `tamID` NULL ,

      `Fname` NULL ,

      `Lname` NULL

      )type=InnoDB row_format=dynamic

      [11:19:30 AM][ 0 ms] create table `riley`.`TAMS` (

      `tamID` NOT NULL ,

      `Fname` NULL ,

      `Lname` NULL ,

      PRIMARY KEY ( `tamID` )

      )type=InnoDB row_format=dynamic

      [11:20:45 AM][ 0 ms] create table `riley`.`TAMS` (

      `tamID` NOT NULL ,

      `Fname` NULL ,

      `Lname` NULL ,

      PRIMARY KEY ( `tamID` )

      )type=InnoDB row_format=DEFAULT

      [11:20:58 AM][ 0 ms] create table `riley`.`TAMS` (

      `tamID` NOT NULL ,

      `Fname` NULL ,

      `Lname` NULL ,

      PRIMARY KEY ( `tamID` )

      )type=ISAM row_format=DEFAULT

      [11:23:28 AM][ 381 ms] create table `riley`.`PAtams` (

      `tamID` bigint NOT NULL AUTO_INCREMENT ,

      `Fname` char NULL ,

      `Lname` char NULL ,

      PRIMARY KEY ( `tamID` )

      )

      [11:23:37 AM][ 0 ms] show databases

      [11:23:37 AM][ 10 ms] show table status from `riley`

      [11:23:41 AM][ 0 ms] show fields from `riley`.`patams`

      [11:23:41 AM][ 0 ms] show keys from `riley`.`patams`

      [11:23:41 AM][ 0 ms] show table status from `riley`

      [11:23:51 AM][ 531 ms] alter table `riley`.`patams` ,drop primary key, add primary key (`tamID` ) ,type=InnoDB row_format=fixed

      [11:23:52 AM][ 0 ms] show databases

      [11:23:52 AM][ 0 ms] show table status from `riley`

      [11:24:17 AM][ 0 ms] show fields from `riley`.`account`

      [11:24:17 AM][ 0 ms] show keys from `riley`.`account`

      [11:24:17 AM][ 0 ms] show create table `riley`.`account`

      Table 2

      [11:14:45 AM][ 0 ms] show databases

      [11:14:47 AM][1232 ms] show table status from `riley`

      [11:14:49 AM][ 0 ms] show table status from `riley`

      [11:14:49 AM][ 20 ms] use `riley`

      [11:14:50 AM][ 180 ms] show fields from `riley`.`account`

      [11:14:50 AM][ 0 ms] show keys from `riley`.`account`

      [11:14:50 AM][ 130 ms] show create table `riley`.`account`

      [11:15:00 AM][ 20 ms] describe `riley`.`account`

      [11:15:00 AM][ 0 ms] show index from `riley`.`account`

      [11:15:05 AM][ 80 ms] use `account`

      [11:17:08 AM][ 0 ms] show fields from `riley`.`account`

      [11:17:08 AM][ 0 ms] show keys from `riley`.`account`

      [11:17:08 AM][ 10 ms] show create table `riley`.`account`

      [11:17:26 AM][ 10 ms] show fields from `riley`.`tams`

      [11:17:26 AM][ 0 ms] show keys from `riley`.`tams`

      [11:17:26 AM][ 0 ms] show create table `riley`.`tams`

      [11:17:26 AM][ 0 ms] describe `riley`.`tams`

      [11:17:26 AM][ 0 ms] show index from `riley`.`tams`

      [11:17:46 AM][ 0 ms] show table status from `riley`

      [11:19:10 AM][ 60 ms] create table `riley`.`TAMS` (

      `tamID` NULL ,

      `Fname` NULL ,

      `Lname` NULL

      )type=InnoDB row_format=dynamic

      [11:19:30 AM][ 0 ms] create table `riley`.`TAMS` (

      `tamID` NOT NULL ,

      `Fname` NULL ,

      `Lname` NULL ,

      PRIMARY KEY ( `tamID` )

      )type=InnoDB row_format=dynamic

      [11:20:45 AM][ 0 ms] create table `riley`.`TAMS` (

      `tamID` NOT NULL ,

      `Fname` NULL ,

      `Lname` NULL ,

      PRIMARY KEY ( `tamID` )

      )type=InnoDB row_format=DEFAULT

      [11:20:58 AM][ 0 ms] create table `riley`.`TAMS` (

      `tamID` NOT NULL ,

      `Fname` NULL ,

      `Lname` NULL ,

      PRIMARY KEY ( `tamID` )

      )type=ISAM row_format=DEFAULT

      [11:23:28 AM][ 381 ms] create table `riley`.`PAtams` (

      `tamID` bigint NOT NULL AUTO_INCREMENT ,

      `Fname` char NULL ,

      `Lname` char NULL ,

      PRIMARY KEY ( `tamID` )

      )

      [11:23:37 AM][ 0 ms] show databases

      [11:23:37 AM][ 10 ms] show table status from `riley`

      [11:23:41 AM][ 0 ms] show fields from `riley`.`patams`

      [11:23:41 AM][ 0 ms] show keys from `riley`.`patams`

      [11:23:41 AM][ 0 ms] show table status from `riley`

      [11:23:51 AM][ 531 ms] alter table `riley`.`patams` ,drop primary key, add primary key (`tamID` ) ,type=InnoDB row_format=fixed

      [11:23:52 AM][ 0 ms] show databases

      [11:23:52 AM][ 0 ms] show table status from `riley`

      [11:24:17 AM][ 0 ms] show fields from `riley`.`account`

      [11:24:17 AM][ 0 ms] show keys from `riley`.`account`

      [11:24:17 AM][ 0 ms] show create table `riley`.`account`

      [11:25:10 AM][ 0 ms] show fields from `riley`.`tams`

      [11:25:10 AM][ 0 ms] show keys from `riley`.`tams`

      [11:25:10 AM][ 0 ms] show create table `riley`.`tams`

    • April 18, 2003 at 5:52 pm #14090
      Ritesh
      Member

      Hello

      The table structure ( or History log ) provided by you doesnt convey much information. Can you send me SQL script of the whole database with information of the columns on which you want to create relationships. You can mail them to [email protected] and I am sure I will come up with an answer 🙂

      HTH

  • Author
    Posts
Viewing 3 reply threads
  • You must be logged in to reply to this topic.