INNODB versus MyISAM

[neutcomp]

Hello,

I have a database made with mySQL -Front. mySQL version is the newest so also with InnoDB.

When looking at the created tables I see as type MyISAM.

I want to use the “Relationship manager” From SQLyog. But it keeps telling me:

The selected table does not have InnoDB Table Handler

You cannot manage relationships for this table

when i look at “3 Objects” there is this information:

TYPE=MyISAM

So my question is how can I get the type set to InnoDB?

Thanxx

Bjorn 😎

[Shadow]

Right click on the table in the object browser and look for the “Change table type to” item. But let me point out that before using InnoDB you should read the related sections of the MySql manual because it's quite simple to get InnoDB working, it is difficult, however, to use and to tune it properly!

[neutcomp]

Oke I managed to change it to InnoDB.

But know I still cannot make a referencekey.

Here is my SQL -Script. Just really easy.

So If someone could tell me what I have to do!

Thanxx

Bjorn 😎

[Shadow]

The tables should not have any records when adding a foreign key constraint. Export the data, build the constraint and import the data.

[neutcomp]

Oke just the normal way?

CREATE TABLE tbl_person(id INT, id_person INT, INDEX person_id (id_person),

FOREIGN KEY (id_person) REFERENCES tbl_company(id_company)

ON DELETE SET NULL

) TYPE=INNODB;

Because with the MYSLyog it wont work. 🙁

Cya

Bjorn 😎

[Shadow]

Interesting, I downloaded your SQL script and after deleting the rows the two tables contained and creating the extra needed index, I was able to create the relationship. Do you have the necessary indices on both fields (the referencing and the referenced)?

BTW, I could suggest a few names for NAC 😛

[neutcomp]

Oke hold on!

Quote:

Do you have the necessary indices on both fields (the referencing and the referenced)?

What do you mean?

Thanxx

Oke, tell me some good names 😛

[Shadow]

MySql does not automatically create the indices required to set up a relationship.

Quote:

Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the FIRST columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly.

For further details see sections 7.5.4.2 Foreign Key Constraints and 3.5.6 Using Foreign Keys in the MySql Manual.

Fehér, Petö, Babos…

[neutcomp]

Code:

DROP TABLE IF EXISTS mediumtabbladen;
CREATE TABLE IF NOT EXISTS mediumtabbladen (
 IdTabladen varchar(4) NOT NULL DEFAULT '' ,
 INDEX i_idtabladen (IdTabladen),
 Tabbladen varchar(20) ,
 PRIMARY KEY (IdTabladen)
)TYPE=InnoDB;

And

Code:

DROP TABLE IF EXISTS sjablonen;
CREATE TABLE IF NOT EXISTS sjablonen (
 idsjabloon int(11) NOT NULL auto_increment,
 INDEX i_idsjabloon (idsjabloon),
 naam varchar(30) NOT NULL DEFAULT '' ,
 tabblad varchar(4) DEFAULT '' ,
 sjabloon longtext ,
 omschrijving varchar(50) NOT NULL DEFAULT '' ,
 PRIMARY KEY (idsjabloon),
  FOREIGN KEY (tabblad) REFERENCES mediumtabbladen (IdTabladen)
  ON UPDATE CASCADE
)TYPE=InnoDB;

Error:

#1005 – Can't create table './bjorn/sjablonen.frm' (errno: 150)

Why does this not work?

I search at the mysql manual:

Quote:

Blank characters are allowed in field names.

It works fine with INNODB tables as long as you do not use these field names in FOREIGN KEY contraints; in this case, blank characters produce the famous message

#1005 – Can't create table '.xxx.frm' (errno: 150)

I hope you or someone els can help me out!

Thanxx

Bjorn 😎

[neutcomp]

Jahooooooooooo 😆

It worked, stuppid InnoDB. The thing you have to do is move the reference key to the top of your create statement.

#

# Table structure for table 'sjablonen'

#

Code:

DROP TABLE IF EXISTS sjablonen;
CREATE TABLE IF NOT EXISTS sjablonen (
 tabblad varchar(4) NOT NULL,
 INDEX i_tabblad (tabblad),
 idsjabloon int(11) NOT NULL auto_increment,
 INDEX i_idsjabloon (idsjabloon),
 naam varchar(30) NOT NULL DEFAULT '' ,  
 sjabloon longtext ,
 omschrijving varchar(50) NOT NULL DEFAULT '' ,
 PRIMARY KEY (idsjabloon),
  FOREIGN KEY (tabblad) REFERENCES mediumtabbladen (IdTabladen)
  ON UPDATE CASCADE
)TYPE=InnoDB;

thanxx

Bjorn 😎

[Shadow]

If you have a primary key on mediumtabbladen.IdTabladen field, then you don't have to add another index on that field as PK itself is an index as well. This goes to the reference field in sjablonen table.

[Shadow]

BTW, giving a second look to your first sjablonen table definition, I discovered that you forgot to create an index on tabblad field, that's why the statement did not work:

DROP TABLE IF EXISTS sjablonen;

CREATE TABLE IF NOT EXISTS sjablonen (

 idsjabloon int(11) NOT NULL auto_increment,

 INDEX i_idsjabloon (idsjabloon),

 naam varchar(30) NOT NULL DEFAULT '' ,

 tabblad varchar(4) DEFAULT '' , <

---

missing index!

 sjabloon longtext ,

 omschrijving varchar(50) NOT NULL DEFAULT '' ,

 PRIMARY KEY (idsjabloon),

  FOREIGN KEY (tabblad) REFERENCES mediumtabbladen (IdTabladen)

  ON UPDATE CASCADE

)TYPE=InnoDB;

[neutcomp]

Quote:

InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. The indexes are needed for foreign key checks to be fast and not require a table scan.

this is from the manual.

So I think its necessary.

I will just check if it workes with one index.

Cya

Bjorn 😎

[Shadow]

Yes, an index is compulsury, but primary keys do create an index!!!!!!

[Author]

Posts