[RESOLVED] Primary Keys

tams

Hi Everyone,
I am just wondering what could be the possible hazards of not having a primary key in MYSQL tables? I am thinking of removing my primary keys from the tables because right now they seem to be a headache, but I don't want to go ahead with it and later regret it.
I have been reading the MYSQL manual to understand primary keys but I am still not totally clear on their usefulness.

Thank you

Piranha

Primary keys are used for a couple of reasons that I don't even have to think about:

To make every row unique. If you don't have a primary key you could have more than one row that have the same value.
To be able to select the exact row. Pretty much like the first one, if you have two rows that is the same it is impossible to get one row, and the right one.
For foreign keys. If the table have a relationship with another table it is essential with primary keys.

Basically you should have primary keys, always. If you use an auto incremented int as primary key you don't have any problem with it, it is automatically handled. But you will have all the advantages that comes with it.

jazz_snob

I think this is pretty obvious, but I'll second Piranha just to make the point clear. pkey is essential.

NogDog

We probably should get a more detailed explanation as to why primary keys "seem to be a headache" for you. While there may be certain (generally rare) cases where you might not want to use a primary key, I would tend to guess (and with the lack of information it's only a guess) that your problem is more deeply rooted in your database design, rather than the use of primary keys.

By the way, note that a primary key can extend across multiple columns, should that be what is required to define the uniqueness of table's rows.

tams

Well when I started using PHP and MYSQL, I didn't know a whole lot about primary keys, So I made lastname field in my table the primary key, and the rest you imagine what happened with the same lastname showing up a couple of times.

Now I have made the id the primary key as this will be unique enough and I will make the employee ID, the foreign key in my other tables.

Pardon my ignorance but I have been using PHP and MYSQL now for a month and a half so I do tend to make stupid mistakes until I become a pro😉

Now if I use "FOREIGN KEY(employee_id) REFERENCES employee(id)" will this give the foreign key the same value as id from first table?

Thank you

Piranha

Then the problem is that you are using the wrong primary key. There is two different ways to use primary keys, natural keys and keys that have got nothing to do with the data.

A key that have got nothing to do with the data is normally an auto incremented inc, it won't cause trouble. But some people don't like it.

A natural key is one or more of the columns of the data in the table. There are two things that you have to consider if you use them. First your primary key have to be unique, a social security number would probably work. Second there could be no way that the value of the primary key may be changed later.

Don't worry if you get some things wrong in the end, it have to be that way in order for you to learn 😃

tams

Thank you for answering. It worked out with the employee id.