creating relationship problem

[phaelium]

Hi,

I'm pretty new to this; I'm trying to create what I believe to be a simple relationship between two tables and I'm getting an error message through sqlyog every time I try to do it. I exported my sql commands as a batch script so you can see what I have.

/*

SQLyog v4.03

Host – 207.184.100.230 : Database – vah service

**************************************************************

Server version 5.0.2-alpha

*/

create database if not exists `vah service`;

use `vah service`;

/*

Table structure for Tech Names

*/

drop table if exists `Tech Names`;

CREATE TABLE `Tech Names` (

`Tech Name` text NOT NULL,

`Tech Number` smallint(6) NOT NULL,

PRIMARY KEY (`Tech Number`)

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

/*

Table structure for Tech Performance

*/

drop table if exists `Tech Performance`;

CREATE TABLE `Tech Performance` (

`Key` int(11) NOT NULL auto_increment,

`Tech Number` smallint(6) NOT NULL,

`Op Code` text,

`Flat Hours` double default NULL,

`Actual Hours` double default NULL,

`Type` text,

`RO` int(11) default NULL,

`Close Date` date default NULL,

PRIMARY KEY (`Key`),

KEY `Tech` (`Tech Number`)

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

What I'm trying to get is the Tech Number PK from Tech Names table to be the foreign key for the Tech Performance field of the same name, but something must be wrong because the relationship manager keeps giving me a Cannot create relation message with some vague reasons why it could be happening.

[CalEvans]

IMHO,

(and take it for what it's worth)

I've always found the relationship manger a weak link in SQLYog. Whenever I have to change things, I usually end up copying the tables over to test (create table test.yaddyadda select * from yaddayadda) dropping and recreating the tables with the relationships intact. Then reloading the data.

Occasionally, I have to use

set foreign_key_checks=0;

before reloading the data but I try not to because the errors help me find problems.

This methodology helps me separate syntax errors 9which usually bite me in the ass every time) with data issues.

HTH,

=C=

[phaelium]

Ok, so how do I take the sql code I have and change it to include the relationships I want?

Then do I just run that code in MySQL?

Thanks for your help 🙂

[Ritesh]

While exporting data using SQLyog, just check the option Set FOREIGN_KEY_CHECKS=0. It will generate the script like:

Code:

/*
SQLyog v4.03
Host – 207.184.100.230 : Database – vah service
**************************************************************
Server version 5.0.2-alpha
*/

create database if not exists `vah service`;

use `vah service`;

set foreign_key_checks = 0;

/*
Table structure for Tech Names
*/

drop table if exists `Tech Names`;
CREATE TABLE `Tech Names` (
`Tech Name` text NOT NULL,
`Tech Number` smallint(6) NOT NULL,
PRIMARY KEY (`Tech Number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

…………..
…………..

set foreign_key_checks = 1;

[Author]

Posts