does PK field type have to be same as FK filed type?

[bazz]

pretty much that is it.
I have a table with a primary key and, in another table, a foreign key to that 1st table needs to be set. should both fields have the same type i.e. where the PK is a 'int(11)' should the FK field be 'int' as well. And if so does it neeed to be limited to 11 as well?

bazz

[Daemonspyre]

In order to have good JOIN syntax, the two fields SHOULD be the same. If they are different, then you are going to have some serious indexing and comparison issues.

Short answer -- Yes. If you have a PK that is INT(11), then your FK should also be INT(11).

[bazz]

Thanks for that. I think it is quite a logical thing but I just wanted to be sure before I delve deeper into MySQL.

bazz

[guelphdad]

Before you get all caught up in the GUI game, INT(11) most likely doesn't mean what you think it does. See this item (http://www.guelphdad.wefixtech.co.uk/sqlhelp/numericdata.shtml) for clarification.

But in general you do have to have the same data type if you are enforcing foreign keys.

You are using innodb and not myisam table types too right?

[bazz]

Thank you and yep, I am using innoDb and I have made all my FK fields the same data type as the primary key to which they refer.

I thought int(11) meant 'an integer with a maximum of 11 characters. Then when I set it to auto-increment, the primary key 'int(11)' autoIncrements. But I shall visit that link to be sure.

er...seems like a few people have made the mistake before me. I just read your article guelphdad and see that because I am not using zero fill, I don't need that (11) at all? I actually only used it because I had done so with VARCHAR and was trying to make my DB leaner/more efficient. A timely reminder that a little knowledge can be a bad thing....;)

bazz

[guelphdad]

many GUIs, phpmyadmin among them, use things like INT(11), SMALLINT(7) etc. etc. most likely because the people programming them didn't know what those values stood for. it won't hurt you to use them, just realize they have nothing to do with the size of the number that can be stored in that field type.