adding foreingkey is error?

[Samanthi perera]

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]

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]

this is the server version i have installed
mysql-5.1.43-win32.msi

[Samanthi perera]

this is i downloaded version

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

[Campbell Ritchie]

You can specify the engine used in MySQL when you create each table.

[Samanthi perera]

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]

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]

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]

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]

i think innodb.
i remember it when i am installing mysql server

[Samanthi perera]

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]

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:

SELECT ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'hr'
AND TABLE_NAME = 'employees'

Regards,

Fatih.

[Samanthi perera]

here is the results

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

[Fatih Keles]

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]

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]

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]

ohoo ,mygod i can't install mysql server now.

[Paul Sturrock]

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]

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]

ya it is working when i use query other than GUI?

http://2.bp.blogspot.com/_bpkZ2BsqXug/S2ryor17OTI/AAAAAAAAAFk/PI7K9N0LW2U/s1600-h/for.GIF