make foreign key a key, really?

[coo_t2]

Hey all. This is probably a dumb question, and one that I should already know but I'll ask it anyway.

If I'm using an bigInt in a table as foreign key, and it is the primary
key in another table, should I make it a "real" key?

Could this really speed queries up? Maybe there's also some other benefits that I don't know about?

tia,
--ed

[r937]

yes, it will improve efficiency on joins

[redemption]

Yup. If it is an actual key (it is unique) in the referencing table.

[coo_t2]

Yup. If it is an actual key (it is unique) in the referencing table.

Not sure which table you mean by "referencing" table.
But I assume you mean the table that has the foreign key.

Say I have two tables.

Table1, and Table2.

Table1
-------------
id | Table2_id | more fields ..


Table2
------------
id(primary key) | more fields...



Table1.Table2_id is referencing Table2.id ..
Table2.id is a primary key.

Does Table1.Table2_id have to be unique?

If a field that references another table has to be unique to
be made a key, is there another way to optimize for joins?

--ed

[r937]

> Does Table1.Table2_id have to be unique?

oh my $deity, it had better not be, otherwise all you have is a zero-or-one-to-one relationship

[redemption]

$deity?

>> If a field that references another table has to be unique to
be made a key, is there another way to optimize for joins?

All keys have to be unique, otherwise they wouldn't actually be "keys" right?


You're right about the referencing table being the table with the foreign key.
Anyway, regarding the uniqueness of Table1.Table2_id, if you wanted to make it a key (like you did in the first place), it had to be unique right?

If Table2_id can have multiple entries for say, "kenny", and "kenny" is of course in the primary key column of Table2, then there is no way you can make Table2_id a key of Table1 right (since you have 2 or more rows with "kenny")? Does that make sense?

[r937]

sorry, no, foreign keys do not have to be unique

but i think the confusion is mysql's fault, which uses the word "key" interchangeably with "index"

using the above examples...

Table2.id is the primary key, therefore it gets a unique index

Table1.Table2_id is a foreign key, which means squat to mysql (except innodb)

however, Table1.Table2_id should get an index to improve join efficiency

okay, here's where it gets ugly

mysql lets you create an index using the word KEY

so Table1.Table2_id is a foreign key which needs a KEY which should not be unique

make more sense?

[redemption]

You can use INDEX in MySQL, so it doesn't make you confused.

In any case, when people say "keys", its more often than not referring to "candidate keys", which are unique.

Rudy, I know foreign keys do not have to be unique (in case you're replying to my post).

[r937]

yes, i was, i was specifically referring to this unfortunate juxtaposition:

>> If a field that references another table has to be unique to
be made a key, is there another way to optimize for joins?

All keys have to be unique, otherwise they wouldn't actually be "keys" right?

it seems to me that the question did seem to be about a foerign key, and that your answer suggested that it had to be unique

i know that you know that foreign keys are typically NOT unique

[redemption]

OK just to make it clear what I'd meant.

Regarding the question "If a field that references another table has to be unique to be made a key, is there another way to optimize for joins?"

I took it to mean that: "If a field that references another table has to be unique to be made a candidate key..."

And so I replied, yes all candidate keys have to be unique. The thought of a "key" as an "index" didn't occur to me.

After all, it already is a foreign key, so I couldn't be commenting on it not being possible to be a foreign key, right? If coo_t2 actually meant to ask "If a field that references another table has to be unique to be made a foreign key, is there another way to optimize for joins?", then I'm terribly wrong, but I don't think that was what he meant.

[r937]

yeah, me too, i'm not sure what a lot of things really mean, eh


:-)

[coo_t2]

I pretty much use phpMyAdmin any time I work with mysql.

So would it be as simple as this? :

1. Hit Table1 link on left.

2. Hit "index" link for "Table1.table2_id" field.

??


--ed

[redemption]

Yup that seems to be correct.

[website]

so 'primary key' is unique
'key' is also unique
but 'index' is not?

and so I continue with making this thread more mess then it is say I have this table

Posts:
id
Users_id
post

Users:
id
name
etc...

Then id in user talbe is a primary key and Users_id in post table is foreign key ?

what is foreign key then? is that just a name for the connection and column that is foreign key is often 'index' but not 'key' ?

Just so confused...

[r937]

yes, foreign key is a special kind of logical relationship

in mysql, key and index are synonymous (i.e. both are a physical index), and both can be unique or not, null or not

a primary key is both logical and physical, and must be unique and not null

[website]

so should I declare Users_id 'key' or 'index' ? what would be the differance

[redemption]

>> Then id in user talbe is a primary key and Users_id in post table is foreign key ?

That seems to be correct, assuming that it's purpose is as it seems, i.e. every Users_id in the Posts table must refer to an id in the Users table.

>> so should I declare Users_id 'key' or 'index' ? what would be the differance

In MySQL, no difference, since it treats KEY and INDEX as synonyms.

[website]

stupid mysql ...

[redemption]

If you have more than 1 key (as in candidate keys), you can (or rather, should) make 1 the PRIMARY KEY and specify the rest as UNIQUE.

Making an INDEX is for physical tuning and has nothing to do with the conceptual schema (whereas PRIMARY KEYs and UNIQUE fields do). It just allows for some optimizations in queries involving the indexed field.

[website]

ok so PRIMARY KEY is just like UNIQUE except that it is the 'primary' key ?
KEY == INDEX and speeds up querys (most often, at least with integer fields) by allowing mysql to search for eg every number starting with 1 but does not have to go down all the list?

is this correct?

[redemption]

>> ok so PRIMARY KEY is just like UNIQUE except that it is the 'primary' key ?

You use these constraints often for candidate keys in your schema, so yes. With the exception that UNIQUE allows for NULL values, while, of course, PRIMARY KEY does not.

Consider the example table User(id, name, email). Suppose id is a key, and so is (name, email) - no user can have the same name AND email. Then you can do this:

CREATE TABLE (
  id INT PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(50),
  UNIQUE (name, email)
)

Of course, you can also make (name, email) the primary key and id UNIQUE.

>> KEY == INDEX and speeds up querys (most often, at least with integer fields) by allowing mysql to search for eg every number starting with 1 but does not have to go down all the list?

How indexes work in MySQL -> http://www.mysql.com/doc/en/MySQL_indexes.html

[r937]

>> Of course, you can also make (name, email) the primary key and id UNIQUE.

well, yes, you could, but you wouldn't

i mean, if (name,email) is the primary key, then what the heck do you need id for?

if, as in so many cases where you have a column called id, it happens to be an auto_increment, then you don't have to bother declaring it unique, because an auto_increment is unique by its very nature...

when you do use an auto_increment id, it does typically have the role of primary key, and in that case it's a surrogate key

thoroughly confused yet?

[redemption]

Yup I truly appreciate that fact, of adding in an extra column just so to get an auto-incrementing field. I make it a point to keep these to a minimum when possible. I've noticed that web-based apps often have an auto-incrementing field which I attribute to the need for easier query strings (think http://www.sitepointforums.com/showthread.php?posttitle=make%20foreign%20key%20a%20key,%20really&postforum=databases ), and also to tutorials and articles that seem to encourage adding an auto-increment field just to get a primary key.

However, like when it is the user ID of the user (which was what I had actually meant by that example, though it was most unfortunately named "id", leading to it sound like an auto_increment column ), which you actually needed to store, or when id represents the identification card no. of a user, then I guess you probably needed it at first.

[website]

but isn't required to have primary key in a table?

[r937]

no, a table is not required to have a primary key

(a relation is, but that's an entirely different thread, and i don't want to go there, because it will eventually take us to dbdebunk again)

you only need a primary key when the database enforces foreign key relationships (which mysql does only for innodb tables) and there is at least one foreign key that references the primary