[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 😎

[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 😎

[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]

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 😛

[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 😎

[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 😎

[Author]

Posts