[dorden]

heather wrote on May 7 2003, 09:04 PM:

Hello,

I have read the threads regarding this topic, but was unable to resolve my problem. I am trying to create a foreign key relationship between tblQuestions.surveyId that references tblSurveys.surveyId. My tables are InnoDB, the surveyId field in both tables is set up as an index, and both fields are the same type and length. When I press “create” after selecting the surveyId fields from the create relationships window, sqlyog goes back to the “Manage Relationships” screen but does not display the relationship I tried to create. I am using mySQL 4.0.1 and sqlyog 3.11. Here is the output of my object window:

Column Information For – db200227.tblQuestions

---

Field Type Null Key Default Extra

---

---

---

---

---

---

id int(10) PRI (NULL) auto_increment

surveyId int(10) MUL 0

qNum varchar(50) YES (NULL)

order int(10) YES (NULL)

logic varchar(50) YES (NULL)

inputType varchar(50) YES (NULL)

responseId int(10) YES (NULL)

question longtext YES (NULL)

Index Information For – db200227.tblQuestions

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment

---

---

---

---

---

---

---

---

---

---

tblQuestions 0 PRIMARY 1 id A 10 (NULL) (NULL)

tblQuestions 1 surveyId 1 surveyId A 1 (NULL) (NULL)

DDL Information For – db200227.tblQuestions

---

Table Create Table

---

---

tblQuestions CREATE TABLE `tblQuestions` (

`id` int(10) NOT NULL auto_increment,

`surveyId` int(10) NOT NULL default '0',

`qNum` varchar(50) default NULL,

`order` int(10) default NULL,

`logic` varchar(50) default NULL,

`inputType` varchar(50) default NULL,

`responseId` int(10) default NULL,

`question` longtext,

PRIMARY KEY (`id`),

KEY `surveyId` (`surveyId`)

TYPE=InnoDB

Column Information For – db200227.tblSurveys

---

Field Type Null Key Default Extra

---

---

---

---

---

---

surveyId int(10) PRI (NULL) auto_increment

title varchar(50) YES (NULL)

intro longtext YES (NULL)

description varchar(50) YES (NULL)

Index Information For – db200227.tblSurveys

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment

---

---

---

---

---

---

---

---

---

---

tblSurveys 0 PRIMARY 1 surveyId A 1 (NULL) (NULL)

tblSurveys 0 surveyId 1 surveyId A 1 (NULL) (NULL)

DDL Information For – db200227.tblSurveys

---

Table Create Table

---

---

tblSurveys CREATE TABLE `tblSurveys` (

`surveyId` int(10) NOT NULL auto_increment,

`title` varchar(50) default NULL,

`intro` longtext,

`description` varchar(50) default NULL,

PRIMARY KEY (`surveyId`),

UNIQUE KEY `surveyId` (`surveyId`) TYPE=InnoDB

All help is appreciated.

Regards,

Heather

🙁 Hello,

I have encounter the same problem.

Mysql 4.0.12-nt

Mylog v3.11

trying to setup relationship one to many between “victoria membership list” (one) and “names and addresses” code details below:

Column Information For – membership.names and addresses

---

Field TypeNull Key Default Extra

---

---

---

---

---

1_listint(10) MUL (NULL) auto_increment

ID int(10) unsigned PRI 0

Title varchar(50) YES (NULL)

FirstName varchar(50) YES (NULL)

Initial varchar(4)YES (NULL)

PreferredName varchar(30) YES (NULL)

Surname varchar(60) YES (NULL)

Position varchar(100) YES (NULL)

Company varchar(160) YES (NULL)

Parent_Company varchar(50) YES MUL (NULL)

Address1 varchar(150) YES (NULL)

Suburbvarchar(50) YES (NULL)

State varchar(25) YES (NULL)

Postcode varchar(6)YES (NULL)

Mobilevarchar(15) YES (NULL)

Telephone varchar(15) YES (NULL)

Fax varchar(15) YES (NULL)

E_Mailvarchar(50) YES (NULL)

Victorian_Executive enum('True','False') YES (NULL)

Victorian_Members_Only enum('True','False') YES (NULL)

Key_Contact_Memberenum('True','False') YES (NULL)

Permanent_Name_Tagenum('True','False') YES (NULL)

Non_Member enum('True','False') YES (NULL)

National_Executiveenum('True','False') YES (NULL)

QueensLand_Member enum('True','False') YES (NULL)

New_South_Wales enum('True','False') YES (NULL)

South_Australia enum('True','False') YES (NULL)

Western_Australia enum('True','False') YES (NULL)

Tasmania enum('True','False') YES (NULL)

ACT enum('True','False') YES (NULL)

Northern_Territoryenum('True','False') YES (NULL)

SA_Executiveenum('True','False') YES (NULL)

Qld_EXecutive enum('True','False') YES (NULL)

NSW_Executive enum('True','False') YES (NULL)

ACT_Executive enum('True','False') YES (NULL)

TAS__Executive enum('True','False') YES (NULL)

NT_Executiveenum('True','False') YES (NULL)

Committee_positionvarchar(50) YES (NULL)

Index Information For – membership.names and addresses

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

names and addresses 0 ID 1 ID A 553 (NULL) (NULL) BTREE

names and addresses 1 1_list 1 1_list A 553 (NULL) (NULL) BTREE

names and addresses 1 Parent_Company 1 Parent_Company A 553 (NULL) (NULL) YES BTREE

DDL Information For – membership.names and addresses

---

Table Create Table

---

names and addresses CREATE TABLE `names and addresses` (

`1_list` int(10) NOT NULL auto_increment,

`ID` int(10) unsigned NOT NULL default '0',

`Title` varchar(50) default NULL,

`FirstName` varchar(50) default NULL,

`Initial` varchar(4) default NULL,

`PreferredName` varchar(30) default NULL,

`Surname` varchar(60) default NULL,

`Position` varchar(100) default NULL,

`Company` varchar(160) default NULL,

`Parent_Company` varchar(50) default NULL,

`Address1` varchar(150) default NULL,

`Suburb` varchar(50) default NULL,

`State` varchar(25) default NULL,

`Postcode` varchar(6) default NULL,

`Mobile` varchar(15) default NULL,

`Telephone` varchar(15) default NULL,

`Fax` varchar(15) default NULL,

`E_Mail` varchar(50) default NULL,

`Victorian_Executive` enum('True','False') default NULL,

`Victorian_Members_Only` enum('True','False') default NULL,

`Key_Contact_Member` enum('True','False') default NULL,

`Permanent_Name_Tag` enum('True','False') default NULL,

`Non_Member` enum('True','False') default NULL,

`National_Executive` enum('True','False') default NULL,

`QueensLand_Member` enum('True','False') default NULL,

`New_South_Wales` enum('True','False') default NULL,

`South_Australia` enum('True','False') default NULL,

`Western_Australia` enum('True','False') default NULL,

`Tasmania` enum('True','False') default NULL,

`ACT` enum('True','False') default NULL,

`Northern_Territory` enum('True','False') default NULL,

`SA_Executive` enum('True','False') default NULL,

`Qld_EXecutive` enum('True','False') default NULL,

`NSW_Executive` enum('True','False') default NULL,

`ACT_Executive` enum('True','False') default NULL,

`TAS__Executive` enum('True','False') default NULL,

`NT_Executive` enum('True','False') default NULL,

`Committee_position` varchar(50) default NULL,

UNIQUE KEY `ID` (`ID`),

KEY `1_list` (`1_list`),

KEY `Parent_Company` (`Parent_Company`)

TYPE=InnoDB ROW_FORMAT=DYNAMIC

Column Information For – membership.victoria membership list

---

Field TypeNull Key Default Extra

---

---

---

---

---

---

1_list int(10) unsigned PRI (NULL) auto_increment

Membership_IDint(10) unsigned 0

Parent_Company varchar(50) UNI

Category varchar(30) YES (NULL)

Address1 varchar(150) YES (NULL)

Suburb varchar(50) YES (NULL)

State varchar(25) YES (NULL)

PostCode varchar(6)YES (NULL)

Mobile varchar(30) YES (NULL)

Telephone varchar(30) YES (NULL)

Telephone2 varchar(30) YES (NULL)

Fax varchar(30) YES (NULL)

E_Mail varchar(100) YES (NULL)

Status varchar(5)YES (NULL)

SubFeePayabledecimal(10,0) YES (NULL)

DatePaid datetime YES (NULL)

AmountPaid decimal(10,0) YES (NULL)

PartPayment decimal(10,0) YES (NULL)

AmountOutstanding decimal(10,0) YES (NULL)

Next_Payment datetime YES (NULL)

DirectACT enum('True','False') YES (NULL)

Service_History textYES (NULL)

CompanyBackground textYES (NULL)

Index Information For – membership.victoria membership list

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

victoria membership list 0 1_list 1 1_list A 393 (NULL) (NULL) BTREE

victoria membership list 0 Parent_Company 1 Parent_Company A 393 (NULL) (NULL) BTREE

DDL Information For – membership.victoria membership list

---

Table Create Table

---

victoria membership list CREATE TABLE `victoria membership list` (

`1_list` int(10) unsigned NOT NULL auto_increment,

`Membership_ID` int(10) unsigned NOT NULL default '0',

`Parent_Company` varchar(50) NOT NULL default '',

`Category` varchar(30) default NULL,

`Address1` varchar(150) default NULL,

`Suburb` varchar(50) default NULL,

`State` varchar(25) default NULL,

`PostCode` varchar(6) default NULL,

`Mobile` varchar(30) default NULL,

`Telephone` varchar(30) default NULL,

`Telephone2` varchar(30) default NULL,

`Fax` varchar(30) default NULL,

`E_Mail` varchar(100) default NULL,

`Status` varchar(5) default NULL,

`SubFeePayable` decimal(10,0) default NULL,

`DatePaid` datetime default NULL,

`AmountPaid` decimal(10,0) default NULL,

`PartPayment` decimal(10,0) default NULL,

`AmountOutstanding` decimal(10,0) default NULL,

`Next_Payment` datetime default NULL,

`DirectACT` enum('True','False') default NULL,

`Service_History` text,

`CompanyBackground` text,

UNIQUE KEY `1_list` (`1_list`),

UNIQUE KEY `Parent_Company` (`Parent_Company`)

TYPE=InnoDB ROW_FORMAT=DYNAMIC

any thoughts would be appreciated

gordon

[Author]

Posts