File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes adding foreingkey is error? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "adding foreingkey is error?" Watch "adding foreingkey is error?" New topic
Author

adding foreingkey is error?

Samanthi perera
Ranch Hand

Joined: Jan 08, 2010
Posts: 510
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

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Samanthi perera
Ranch Hand

Joined: Jan 08, 2010
Posts: 510
this is the server version i have installed
mysql-5.1.43-win32.msi
Samanthi perera
Ranch Hand

Joined: Jan 08, 2010
Posts: 510
this is i downloaded version

http://3.bp.blogspot.com/_bpkZ2BsqXug/S2l-Mn7BitI/AAAAAAAAADU/sRvwxESEt18/s1600-h/my.GIF
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38840
    
  23
You can specify the engine used in MySQL when you create each table.
Samanthi perera
Ranch Hand

Joined: Jan 08, 2010
Posts: 510
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

Joined: Apr 14, 2004
Posts: 10336

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).
Samanthi perera
Ranch Hand

Joined: Jan 08, 2010
Posts: 510
here are the steps when i configuring mysql server

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2mhuuwsSbI/AAAAAAAAADs/ecKJxMZ62XA/s1600-h/a.GIF

http://1.bp.blogspot.com/_bpkZ2BsqXug/S2mh2RscZPI/AAAAAAAAAD0/5MSxMs66Wjo/s1600-h/b.GIF

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2miNC62h8I/AAAAAAAAAD8/A34sI1XKJU8/s1600-h/c.GIF

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2miSe2BqPI/AAAAAAAAAEE/jLF26ubaX1k/s1600-h/d.GIF

http://1.bp.blogspot.com/_bpkZ2BsqXug/S2mi6-NqYAI/AAAAAAAAAEU/MBvRWnYaVj8/s1600-h/e.GIF

http://3.bp.blogspot.com/_bpkZ2BsqXug/S2mjmfp2UDI/AAAAAAAAAEc/mc5uj8WazTY/s1600-h/11.GIF







Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Jan 08, 2010
Posts: 510
i think innodb.
i remember it when i am installing mysql server
Samanthi perera
Ranch Hand

Joined: Jan 08, 2010
Posts: 510
i have installed mysql server again

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2mnNo7AEPI/AAAAAAAAAEk/lJojTWleszI/s1600-h/a.GIF

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2mnOzCPz_I/AAAAAAAAAEs/dzGgJ4ltHME/s1600-h/b.GIF


http://4.bp.blogspot.com/_bpkZ2BsqXug/S2mnoWHzOpI/AAAAAAAAAE0/gBpilVxikC4/s1600-h/c.GIF

http://1.bp.blogspot.com/_bpkZ2BsqXug/S2mnyJa94ZI/AAAAAAAAAE8/ooSgC9w_4Ew/s1600-h/d.GIF
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
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

Joined: Jan 08, 2010
Posts: 510
here is the results

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2os7sivitI/AAAAAAAAAFE/WNPQ9IJad8s/s1600-h/ino.GIF
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
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

Joined: Jan 08, 2010
Posts: 510
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Jan 08, 2010
Posts: 510
ohoo ,mygod i can't install mysql server now.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Jan 08, 2010
Posts: 510
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

Joined: Jan 08, 2010
Posts: 510
ya it is working when i use query other than GUI?

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2ryor17OTI/AAAAAAAAAFk/PI7K9N0LW2U/s1600-h/for.GIF
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: adding foreingkey is error?