Creating Foreign Key Relationships

This topic is empty.

Viewing 8 reply threads

Author

Posts
- May 7, 2003 at 9:04 pm #7906
  
  heather
  Member
  
  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
- May 7, 2003 at 9:05 pm #14210
  
  CalEvans
  Member
  
  Do you get any errors when creating the table?
  
  =C=
- May 7, 2003 at 9:16 pm #14211
  
  heather
  Member
  
  No errors at all, when creating the relationship (or lack of) or when creating the tables. My overall goal is to migrate an Access database to a linux box so I'm trying to re-create it in mySQL. I used the import utility of sqlyog initially. Then I went through and added the primary keys that it lost and changed data types that didn't import correctly. The last step that I can't seem to replicate is the relationships. Ahh!
- May 7, 2003 at 9:30 pm #14212
  
  CalEvans
  Member
  
  I was able to create the two tables in my test database and then create the relationship.
  
  To create the relationship, I clicked on tblQuestions and then on the Manage Relationships button. Clicked the New Button, selected the two fields and the actions I wanted.
  
  Not sure why it's not working for you. (code below.)
  
  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`),
  
  FOREIGN KEY (`surveyId`) REFERENCES `test.tblSurveys` (`surveyId`) ON DELETE CASCADE
  
  TYPE=InnoDB
- May 7, 2003 at 10:35 pm #14213
  
  heather
  Member
  
  What version of sqlyog are you using? Can you please check that you copied all of the code to the forum? I keep getting a syntax error and there seems to be an open paren without a close.
  
  Thanks,
  
  Heather
- May 8, 2003 at 1:50 pm #14214
  
  Ritesh
  Member
  
  Hello
  
  Execute the following statement for the database that has the concerned tables.
  
  Code:
  
  show table status from `yourdbname`
  
  Do you see any FK defination in the comment field of the resultset?
  
  Better still if you can post the result out here 😀
- May 8, 2003 at 2:01 pm #14215
  
  CalEvans
  Member
  
  I'm using 3.11. Yes, there is a mising ). It's a known issue. Sorry, I should have corrected the code before I posted.
  
  change:
  
  Code:
  
  TYPE=InnoDB
  
  to
  
  Code:
  
  ) TYPE=InnoDB
  
  The the code should work.
  
  =C=
- May 21, 2003 at 12:22 pm #14216
  
  dorden
  Member
  
  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
- May 28, 2003 at 1:03 am #14217
  
  jvieira
  Member
  
  soryr i solved my problem,….
  
  had a post here but now i solved it,,.
  
  i had a double FK and had a problem.
Author

Posts