• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

adding foreingkey is error?

 
Samanthi perera
Ranch Hand
Posts: 510
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this is my deparment table screenshot
http://1.bp.blogspot.com/_bpkZ2BsqXug/S2l45oGT7oI/AAAAAAAAAC0/TlteKJ0WO9k/s1600-h/dep.GIF


this is my eployee table screenshot
http://1.bp.blogspot.com/_bpkZ2BsqXug/S2l5SfqIIqI/AAAAAAAAADM/aDg6xhnD43A/s1600-h/emp.GIF

this is i am going to change the employee table.I need to add the forieng key.
http://4.bp.blogspot.com/_bpkZ2BsqXug/S2l5BEEgZHI/AAAAAAAAAC8/z1cZYXG0_EA/s1600-h/for.GIF


this is the message when i get after clicking Apply changes
http://4.bp.blogspot.com/_bpkZ2BsqXug/S2l5LYBvO4I/AAAAAAAAADE/CzMTTl9w-JA/s1600-h/ch.GIF
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you using a MySQL database engine that supports foreign key constraints? Whatever GUI you are using might not create them if it is for example the MyISAM engine.
 
Samanthi perera
Ranch Hand
Posts: 510
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this is the server version i have installed
mysql-5.1.43-win32.msi
 
Samanthi perera
Ranch Hand
Posts: 510
 
Campbell Ritchie
Sheriff
Posts: 48382
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can specify the engine used in MySQL when you create each table.
 
Samanthi perera
Ranch Hand
Posts: 510
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i don't understand what you mean specifi when table creating?
i tried giving foreing key when creating table.But it is not working.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQL contains a bunch of different database storage engines you can use (e.g. MyISAM, InnoDB etc.). These have different features; the MyISAM database engine for example doesn't implement relational constraints. This is just a guess, but your GUI tool might be clever enough to spot you are using the MyISAM engine (if you are) and not applying the constraint because it does nothing.

If you are not sure what storage engines are, I'd take the time to have a quick read of the documentation covering them. You will also find the syntax for specifying the engining you want to use when you create the table (or changing the default).
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, looks like the InnoDB engine you are using.

What happens when you try scripting the FK manually rather than using the GUI? Does that work.
 
Samanthi perera
Ranch Hand
Posts: 510
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i think innodb.
i remember it when i am installing mysql server
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I did not understand how did you agreed that it is InnoDB from the screenshots, I could not seen any thing like that
As far as I remember, mysql versions 5.0.x and previous releases uses MyIsam as the default storage engine. If you want to use Berkeley or InnoDB you have to edit my.ini file.

You have to check it first, if you see MyIsam, you can not use FK as it is not supported. Your GUI tool is a bundled tool I guess, which should show the storage engine may be in "table options" tab, have a look at it.

or alternatively you may try this:


Regards,

Fatih.
 
Samanthi perera
Ranch Hand
Posts: 510
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well then you were right, it is InnoDB. So back to Paul's question, What happens when you try to add FK with script?

Here you may find it useful : http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Regards,

Fatih.
 
Samanthi perera
Ranch Hand
Posts: 510
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am now installed mysql server again.
now i have to configure it.
what shuld i select from this ?

http://1.bp.blogspot.com/_bpkZ2BsqXug/S2p29w3xShI/AAAAAAAAAFM/2lFQgZ90xwI/s1600-h/inn.GIF
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What happens when you try scripting the FK manually rather than using the GUI? Does that work?

The reason I ask is the messages from the GUI could just be confusing the issue.
 
Samanthi perera
Ranch Hand
Posts: 510
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ohoo ,mygod i can't install mysql server now.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samanthi perera wrote:ohoo ,mygod i can't install mysql server now.

Sorry, I'm not following what you are trying to do here. Why did you uninstall it?
 
Samanthi perera
Ranch Hand
Posts: 510
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it is ok.finally i avoid it.
i think if i use query it is working

http://1.bp.blogspot.com/_bpkZ2BsqXug/S2rp42055yI/AAAAAAAAAFc/JaSKGNwf7zY/s1600-h/fk.GIF
 
Samanthi perera
Ranch Hand
Posts: 510
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ya it is working when i use query other than GUI?

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2ryor17OTI/AAAAAAAAAFk/PI7K9N0LW2U/s1600-h/for.GIF
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic