Manage Relationships

[Neshi]

Hello!

Has anybody experiences in managing relationships!? Especially with the foreign key option?!

I tried everything but it is not working for me 🙁 🙁 🙁

Regards!

[Medozas]

Sure, here I am… Had problems myself too, but whats your problem…. show me in sql your create table scripts and what kind of relationship you need…

[CalEvans]

Yes, I've used it extensivly to build relationships. The most common problem I run across is forgetting to build the index first.

Exactly what problem are you having?

=C=

[Neshi]

I can't create foreign keys!

I always get the following message:

Could not create relation. The possible causes of error are –

1) the reference table does not have InnoDB table handler

2) no index was found on selected columns. you need to explicitly create index on selected columns

3) the selected columns will create incorrect foreign defination

5)you did not select correct columns

4) selected columns were not found in the reference table.

@CalEvans: what do you mean with build the index first? Every column I want to use for foreign key has an index!!! What kind of index does it have to be?

@Medozas: I don't understand what I should show you? Do you want to have all tables with their properties?!

Thank you!!!!!!!!!!!

[Jez]

Hi, Neshi

Connect to your MySQL database with SQLyog and use the SQLyog Object Browser to locate a pair tables which you are trying to relate with primary and foreign keys.

Double-click the first table and then single-click in the Objects pane at the bottom of the SQLyog window. Press Ctrl + A to select all the text in the pan and then Ctrl + V to paste this into your a post to this forum.

Repeat this process with the second table and send us your posting.

We can then determine whether it's the SQL statements which are at fault. If not, it may be a configuration issue with MySQL, but let's cross that bridge if and when we get to it…

Jez

[Medozas]

Yup YEZ has it… Do exactly he says… You know, to help you we got to know, what your problem is… But just if you are a newbie: You need mysql 4.x to manage foreign keys, anything 3.x will not work…

besides that, as in your message you have to change your table type to InnoDB, just do that with a right click on your table, then “Change table type to” –> “InnoDB”

After that, check that you have indexes on the columns you wish to put in relationship.

there are more hints and tricks to watch out for (unique, not null, etc… ) but just give us the SQL-Script, then we'll tell you…

greetz

[Neshi]

I solved the problem! I created lots of indices (sometimes twice or three times, now I have to find out which one I can delete!). I am using InnoDB table type and MySQL 3.23-max-nt!!!!

[Jez]

Hi, Neshi

Glad you've got those keys sorted.

As you've discovered, you don't actually need version 4 of MySQL to use InnoDB and pk/fk relationships. They've been available in the source code version of MySQL since 3.23.34a. If you're using a recent version (e.g. 3.23.55 or 3.23.56) you also shouldn't have to use the Max Windows binary – I've found the mysqld and mysqld-nt version both support InnoDB and pk/fk relationships.

Having said that version 4 is a bit easier to set up, although if you're working exclusively with InnoDB you will need to add the default-table-type=innodb option in the my.ini file to set InnoDB as the default table type.

Jez

[Neshi]

I am really glad that I solved the problem! I really hope it is working! I will test the database soon….

Well it was really a little bit tricky to use the inno tables… I changed the my.ini, but it is not working exactly! For example the logs are not saved to my iblog directorty but in another directory. It seems as if it's not reading the ini file correctly…

[Neshi]

I need help!!! It is not working at the moment!!!

Here a copy of the table information

Table 1:

Column Information For – datenstand

---

Field Type Null Key Default Extra

---

---

---

---

---

---

Datenstand_ID bigint(6) unsigned PRI (NULL) auto_increment

Datenstand varchar(50) YES 0

Baureihe_ID bigint(6) unsigned MUL 0

Index Information For – datenstand

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment

---

---

---

---

---

---

---

---

---

---

datenstand 0 PRIMARY 1 Datenstand_ID A 6 (NULL) (NULL)

datenstand 0 DatenstandID 1 Datenstand_ID A 6 (NULL) (NULL)

datenstand 1 Baureihe_ID 1 Baureihe_ID A 2 (NULL) (NULL)

DDL Information For – datenstand

---

Table Create Table

---

---

datenstand CREATE TABLE `datenstand` (

`Datenstand_ID` bigint(6) unsigned NOT NULL auto_increment,

`Datenstand` varchar(50) default '0',

`Baureihe_ID` bigint(6) unsigned NOT NULL default '0',

PRIMARY KEY (`Datenstand_ID`),

UNIQUE KEY `DatenstandID` (`Datenstand_ID`),

KEY `Baureihe_ID` (`Baureihe_ID`)

TYPE=InnoDB ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 21504 kB'

Information table 2:

Column Information For – baureihe

---

Field Type Null Key Default Extra

---

---

---

---

---

---

Baureihe_ID bigint(6) unsigned PRI (NULL) auto_increment

Baureihe varchar(50) YES 0

Index Information For – tolassist.baureihe

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment

---

---

---

---

---

---

---

---

---

---

baureihe 0 PRIMARY 1 Baureihe_ID A 4 (NULL) (NULL)

baureihe 0 BaureiheID 1 Baureihe_ID A 4 (NULL) (NULL)

baureihe 1 Baureihe_ID 1 Baureihe_ID A 4 (NULL) (NULL)

DDL Information For – baureihe

---

Table Create Table

---

---

baureihe CREATE TABLE `baureihe` (

`Baureihe_ID` bigint(6) unsigned NOT NULL auto_increment,

`Baureihe` varchar(50) default '0',

PRIMARY KEY (`Baureihe_ID`),

UNIQUE KEY `BaureiheID` (`Baureihe_ID`),

KEY `Baureihe_ID` (`Baureihe_ID`)

TYPE=InnoDB

I want to have a relationship between the Baureihe_IDs! Baureihe_ID should be foreign key in the table datenstand !!!!!!!!

I hope somebody can help me 😮 😮 😮

[alfie romeo]

Hello

I got the same problem trying to link two tables.

here is table 1) jokes.authors:

Column Information For – jokes.authors

---

Field Type Null Key Default Extra

---

---

---

---

---

---

ID int(11) PRI (NULL) auto_increment

Name varchar(255) YES (NULL)

Index Information For – jokes.authors

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment

---

---

---

---

---

---

---

---

---

---

authors 0 PRIMARY 1 ID A 5 (NULL) (NULL)

DDL Information For – jokes.authors

---

Table Create Table

---

---

authors CREATE TABLE `authors` (

`ID` int(11) NOT NULL auto_increment,

`Name` varchar(255) default NULL,

PRIMARY KEY (`ID`)

TYPE=InnoDB

here is table two jokes.emails:

Column Information For – jokes.emails

---

Field Type Null Key Default Extra

---

---

---

---

---

---

ID int(11) PRI 0

EMail varchar(255) YES (NULL)

AID varchar(10) YES MUL (NULL)

Index Information For – jokes.emails

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment

---

---

---

---

---

---

---

---

---

---

emails 0 PRIMARY 1 ID A 2 (NULL) (NULL)

emails 1 Foreign 1 AID A 2 (NULL) (NULL)

DDL Information For – jokes.emails

---

Table Create Table

---

---

emails CREATE TABLE `emails` (

`ID` int(11) NOT NULL default '0',

`EMail` varchar(255) default NULL,

`AID` varchar(10) default NULL,

PRIMARY KEY (`ID`),

KEY `Foreign` (`AID`)

TYPE=InnoDB

I'm trying to get field AID in jokes.emails to read ID in jokes.authors. I'm a novice so not too sure.

many thanks

[Author]

Posts