Id As Primary Key

The Id thin make me confused. I have a little problem bout ID.

For example, i want to make a table for 'member' consists of attribut such us :

Table Member, with attribut :
member_code
member_name
member_address
member_email

All of those attributs above could be added or edited by user or administrator. Should I add one attribut named 'id' as the primary key, although the member_code is already unique that could differ every record of the table?

So Far i usually add 'id' (auto_increment) as the primary key. Why?? Because I think we need some uniqid as prymary key that untouchable by user or administrator but computer. So the prymary key is generated by computer. I think that right to avoid the human error on generate the uniqid.
But, I just think it is useless because the member_code could differ every record from the table. Its already unique.

So thats makes me confuse. Should I add 'id' or not?

I need some suggestions please....

Tengkyu....

a primary key is always good to have... but it sounds like you could really make your member_code a primary key no?

Make shure you'll never have to change your member codes or it will turn in messing up with PK and indexing.
There are also other possible dangers ...

oh yes that is true if you have to change the member_code... but if the member_code is unique already wouldn't it be weird to change it? I guess there are a bunch of cases where you would change a unique value but I cannot think of any where you would do it on a table without a primary key... or at least I would not trust doing it without a primary key.

PKs should NEVER change as pabloj mentioned.

Simple rules on whether or not to use an auto-generated PK:

1) Is there any other field in the table that is UNIQUE and will NEVER change?
2) If so, is that field of a type suitable to be used in other tables as a reference?
3) If not, use the auto-generated PK
4) If in doubt, use the auto-generated PK

I have a guestion about indexing. I always create an auto incremented column for and id number to be used as a primary key. But recently I saw a php script used to create a mysql table that looked like this:

The primary key is set up there just as I would have done it, but this author also added a "unique key" using the primary key. I dont understand why. What is the benefit of that, and how does it work?

There is no benefit. PKs are unique by definition. The script author is confused.

criss,

I tend to believe that using natural primary keys is often better than an auto-generated one. By 'natural', I mean that the key already has meaning in your database model, as in 'member_code' above.

Yes, rodk is right that you should be careful about the potential for your primary key values to change.

But, even if those values can change, this is not necessarily a problem if your database system has full referential integrity. Most DBMS systems with full referential integrity can force an update of all relate values, when you change the primary key on which they reference. But, if these values are updated too much, this of course could have bad performance implications.

It looks like you are using MySQL. Are you using InnoDB tables with referential integrity? Are you enforcing referential integrity between your tables?

Another note: Some people like to name the primary key in every table 'id'. I think this is a bad design choice. Every table should have a unique name for its primary key, and every table that references that primary key should use the same name. This avoids a lot of confusion as your app gets larger.

For example, if you 'invoices' table has an 'id' column, and you 'customers' table has an 'id' column, then queries which join the two start to look ugly. Also, new every time you reference that key in another table, you will have to call it something like 'invoices_id' and 'customers_id'. I think it is much more natural that the key should have the same name wherever it is used or referenced, and that every primary key should have a unique name in the database.

rod's point #2 -- is that field of a type suitable to be used in other tables as a reference? -- is a very important point

if you find that your natural primary key is a composite (more than one column), then a surrogate key (e.g. auto_increment) is often better, simply from the point of view of efficiency of joins

however, don't forget that when you have a surrogate primary key, it is usually important to declare a unique index on the "real" key -- which is now called a candidate key (a slightly inappropriate name, but more acceptable than "the real pk" which i usually call it)

this failure to add that additional unique index is what leads people to discover that their table eventually has "duplicates"

as for the naming issue, i agree with rycamor -- but it's really six of one versus a half dozen of the other, as there are pros and cons for both schemes (calling every pk "id" versus "foo_id" and "bar_id" etc.)

the only place it really makes a difference is if the database supports natural joins, which you won't be able to use if you call every pk "id"

rudy
http://r937.com/

So its depend on the database relation structure and the database integrity.

Ok, thanx for all

Just one minor comment about the id column name: you should always reference columns in DML sentences referencing the name of the table owner of the column, that is:
SELECT tablename.column1name, tablename.column2name,....

especially when you are joining two (or more) tables. Why? Because if in the future you update the structure of any of the tables and there is a column name that is repeteaded (that wasn't before) and you didn't explicitely indicated the table name in the instructions, you will receive an error message, as the database won't be able to decide which of the columns are you referring to, so you will be forced to update your query (update, etc) even if you want to do exactly the same than before. That's why is not confusing to use the name "id" for all the columns in the tables with automatic ids, because you will reference them as "employee.id", "customer.id", etc; otherwise it would be customer.customer_id, employee.employee_id which is a lot of duplication ... you only use a prefix, as customer_id, if you are referencing the id of the customer table in another table (fk column).
In this way you avoid to update your code when you don't have to change its functionality!

Hope this helps!

Cheers,
Ana