6.13 Schema Designer

[Bruce Leister]

Hi, been having a play, very nice tool.

I have three tables:

table A – primary key Field_0, foreign key Field_1

table B – primary key Field_1, Field_2, also a non unique index on Field_2

table C – primary key Field_2

I can not get the relationships to show

1. the many-many between tables A/B on Field_1

2. the many-one between tables B/C on Field_2

See attached screen shot.

Is this WAD ? (working as designed) or a bug? or I do not know how to use the tool ?

Bruce Leister

[peterlaursen]

I am sorry but I am not able to understand!

In this

table A – primary key Field_0, foreign key Field_1

table B – primary key Field_1, Field_2, also a non unique index on Field_2

table C – primary key Field_2

.. please change to the names actually used in the screenshot. And if FK's are defined please paste table create statement!

[Bruce Leister]

These are the three table definitions,

badge_readers are many to many to areas on area field

and areas is many to one to categories on category

table badge_readers – primary key badge_reader, also a foreign key on area

table areas – primary key area,category, also a non unique index(foreign key) on category

table categories – primary key category

Bruce.

[codebox]CREATE TABLE `badge_readers` (

`badge_reader` char(8) NOT NULL,

`site` char(2) NOT NULL,

`floor` char(2) NOT NULL,

`location` char(20) NOT NULL,

`area` char(40) NOT NULL,

PRIMARY KEY (`badge_reader`),

KEY `FK_badge_readers` (`area`),

CONSTRAINT `FK_badge_readers` FOREIGN KEY (`area`) REFERENCES `areas` (`area`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC [/codebox]

[codebox]CREATE TABLE `areas` (

`area` char(40) NOT NULL,

`category` char(40) NOT NULL,

PRIMARY KEY (`area`,`category`),

KEY `FK_areas` (`category`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC[/codebox]

[codebox]CREATE TABLE `categories` (

`category` char(40) NOT NULL,

`owner_uid` char(9) NOT NULL,

`owner_email` char(60) NOT NULL,

`backup_uid` char(9) NOT NULL,

`backup_email` char(60) NOT NULL,

PRIMARY KEY (`category`),

CONSTRAINT `FK_categories` FOREIGN KEY (`category`) REFERENCES `areas` (`category`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC[/codebox]

[peterlaursen]

1)

desired 'many-one' between tables B/C on Field_2″ becoming 'one-one'

The problem is this (if I understand correctly)

CREATE TABLE `areas` … PRIMARY KEY (`category`,`area`),

There is a primary key defined spanning two rows. Including the row that is referenced in the Foreign Key CONSTRAINT in another table. That is why you get a one-to-one relationsship. Can you tell how that PK with that definition came into existence?

2)

desired 'many-many' between tables A/B on Field_1 becoming 'many-one'

Whether 'one' or 'many' is displayed depends on the type of index existing on a column. To create a many-many the indexes on both columns must be non-unique.

We have a report similar to 1) in our ticket system, so there may be something to be corrected or clarified!

[peterlaursen]

edit:

This post was removed. It was a silly mistake only!

— peter

[peterlaursen]

But there seems to be introduced a bug in 'Manage relationships' dialogue recently (while developing Schema Designer?)

After execute those create stmts:

CREATE TABLE `parent` (

`id` bigint(20) NOT NULL auto_increment,

`t1` varchar(20) NOT NULL,

`t2` varchar(20) NOT NULL,

PRIMARY KEY (`id`,`t1`),

UNIQUE KEY `t2` (`t2`) — note: this key was added as compared to example above!

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (

`id` bigint(20) NOT NULL auto_increment,

`t1_child` varchar(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The dialogue displays like attached. It is not possible to 'blank' the columns in the target.

How to define a FK from child.t1_child to parent.t2 ?

[attachment=782:fkcreate.jpg]

[peterlaursen]

Here is an example using dual column PK that works (with 'manange relationships' and Schema Designer):

/*normal construction*/

CREATE TABLE `parent` (

`id` bigint(20) NOT NULL auto_increment,

`t1` varchar(20) NOT NULL,

`t2` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `t1` (`t1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (

`id` bigint(20) NOT NULL auto_increment,

`t1_child` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `t1_child` (`t1_child`),

CONSTRAINT `FK_child` FOREIGN KEY (`t1_child`) REFERENCES `parent`(`t1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* cleaning up*/

DROP TABLE child;

DROP TABLE parent;

/* dual colunm PK in target */

CREATE TABLE `parent` (

`id` bigint(20) NOT NULL auto_increment,

`t1` varchar(20) NOT NULL,

`t2` varchar(20) NOT NULL,

PRIMARY KEY (`id`,`t1`),

UNIQUE KEY `t1` (`t1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (

`id` bigint(20) NOT NULL auto_increment,

`t1_child` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `t1_child` (`t1_child`),

CONSTRAINT `FK_child` FOREIGN KEY (`t1_child`) REFERENCES `parent`(`t1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* we try to provoke an error */

ALTER TABLE `child` DROP FOREIGN KEY `FK_child` ;

ALTER TABLE `child` add constraint `FK_child` FOREIGN KEY (`t1_child`) REFERENCES `parent` (`t1`)

[attachment=783:expected.jpg]

Seems that we have two issues here:

1)

In your example there are two FK's from each their table referencing two different columns that together constitute this 'dual column' PK in the same table. This seems to 'fool' the Schema Designer

2)

In my example the 'manage relationships' dialogue gets fooled by a specific key setup.

[peterlaursen]

Please verfiy the fix for this with 6.14 RC2.

http://www.webyog.com/forums//index.php?showtopic=3640

As regards the issue with the 'manage relationships' dialogue it is per design – only I became confused! The dialogue will display columns defined as FK. You can remove either by selecting it and pressing . However we will consider how to improve intuity in this respect!

[Author]

Posts