• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL Error

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Guys and Ladies(of course!)

I created the table lieferanten successfully! Like this



Now when try to create the table "lieferanten_roles" I got this error:


mysql> CREATE TABLE Lieferanten_roles(
-> P_Id int(8) NOT NULL AUTO_INCREMENT,
-> login varchar(15) NOT NULL,
-> roles varchar(10) NOT NULL,
-> PRIMARY KEY (P_Id),
-> FOREIGN KEY (login) references lieferanten(login)
-> on update cascade on delete cascade);
ERROR 1005 (HY000): Can't create table 'webapp.lieferanten_roles' (errno: 150)
mysql>



I know there is a problem with the foreign key occured. But I can not check it

Do anybody have an idea?
 
author
Posts: 5856
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Perhaps a capitalization error? Some installations of MySQL, especially on Linux, are case sensitive.

The table name is: Lieferanten
The foreign key: "references lieferanten(login)"
 
Gerry Mueller
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank for your answer. I am working under windows.
But I solve the problem proceeding otherweise.

Thanks
 
Peter Johnson
author
Posts: 5856
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Gerry, please describe what you did to solve the problem as a courtesy for future Ranchers who have the same problem you had an come across your post.
 
Gerry Mueller
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
HI Johnson,

excuse you're correct!

In the fact, the problem concert the foreign key. When setting a foreign key the primary key in the index-table (here lieferanten and key login)
must be the first column.

I got it when enter the following command in the MySQL Command Line Client: "show innodb status" and read the paragrap
last innodb error or something like that.

then my tables now look like follow:;


CREATE TABLE Lieferanten(
login varchar(15) NOT NULL,
P_Id int(8) NOT NULL,
firm varchar(50) NOT NULL,
contact_person varchar(50) NOT NULL,
telefon varchar(20) NOT NULL,
pwd varchar(15) NOT NULL,
email varchar(20) NOT NULL,
PRIMARY KEY (login)
);

ALTER TABLE Lieferanten AUTO_INCREMENT=1000;

----------

CREATE TABLE Lieferanten_roles(
P_Id int(8) NOT NULL AUTO_INCREMENT,
login varchar(15) NOT NULL,
roles varchar(10) NOT NULL,
PRIMARY KEY (P_Id),
FOREIGN KEY (login) references lieferanten(login)
on update cascade on delete cascade);



And it work!
Bye bye!
 
reply
    Bookmark Topic Watch Topic
  • New Topic