primary, unique and index

[Anon]

basic question:

When creating a mySQL table, when should I use primary, unique and index field characteristics? I have not been able to find a good explaination of these options; I want to make sure my tables are sound.

If I make a field unique, and it means what it says, then will other entries to that
field of the same value produce an error?

what is a good resource for basic table/database creation besides the php and mySQL websites? I have not found anything useful there.

thanks,
Chris

[[deleted]]

I've allways found teh manual quite usefull :-)

A primary key is also a unique constrant.
That means, the primary key column cannot contain duplicates,
trying to insert a duplicate value will result in an error.
Same for the unique, it cannot contain duplicates.

Indexes are something else.
An index is like a table of contents for the data in your table.
This is used by the database engine to lookup data without having to
actually read all the data in the table.
Indexes are usually created using only a few of the columns in the actual table,
and therefore they are much smaller than the real table. Therefore they are faster.

[lawnmowerman]

These are very complex and in-depth questions you have asked. Let me try to skim over them and give you pointers to more information.

Primary key columns contain unique values and are automagically indexed. A primary key should be the column which you can use to identify an individual row with 100% certainty.

Search for "primary key" in the "CREATE TABLE" documentation on MySQL's site:
http://www.mysql.com/doc/C/R/CREATE_TABLE.html

Unique columns require that the data you insert or update in the table is not duplicated anywhere else in the column.

Search for "unique" in the "CREATE TABLE" docs I mentioned above for more info.

If you try to insert or update data which does not conform to these rules (you try to add a customer with custID=1 when there is already a customer with custID=1), an error will be returned and the row not inserted or updated.

Indexed columns are columns which are presorted at the time you insert or update a row. This significantly speeds up correctly formed queries.

Check out Tim Perdue's article on Database Indexing for more information:
http://www.phpbuilder.com/columns/tim20010110.php3

You may also want to consider picking up a book I've found very useful (though other's may disagree grin) called "Teach Yourself SQL in 10 Minutes" from SAMS.
http://www.samspublishing.com
There are quite a few typos in the book, but otherwise provides a good, general overview of SQL syntaxes. The author even goes as far as to mention how different SQL servers expect or require a different syntax. A good buy for $12.99 US! Even though the book is only an overview and does not cover many of the more complex aspects of SQL, reading the MySQL manual after taking the time to read and understand this book becomes much easier.

I hope this helps!

-Rich

[smarlowe]

A good SQL primer is "SQL for Smarties". It explains the basics of SQL database theory very well, and has lots of very useful examples. Note that about 90% of the advanced examples need a database that can do unions/subselects/transactions or other things mysql doesn't have, but for the first four or five chapters mySQL is just fine.

[Anon]

what is the difference between primary and unique?

If you have the time, use this example table to explain how I would use each type of field.

id | name | city | state | DNA code
1 joe woods PA 123
2 sam cave PA 456
3 jon cave PA 789

Thank you very much for your help,
Chris

[Anon]

So, is a primary field a combination of a unique field and an indexed field to reduce the amount of code necessary?

If you have the time, use this example table to explain how I would use each type of field.

id | name | city | state | DNA code
1 joe woods PA 123
2 sam cave PA 456
3 jon cave PA 789

Thank you very much for your help,
Chris

[lawnmowerman]

Well, the id column should be your primary key, and therefore unique. It is generally a good idea to have an autoincrementing primary key for each table to ensure row individuality.

DNA code would best be set as unique, assuming every person's DNA is different.

Indexing is determined by your query needs.

If you're going to be selecting rows sorted by state and city, then you may want to make an index of "state,city"
in your table which is covered in Tim's article.

If you are only going to select rows based on name, then make an index of "name".

You can have several indexes (indices?) for each table.
The primary key is always an index (the MySQL docs explain that "key" is the same as "index"). If you're going to select rows sometimes based on the name column and sometimes based on the state and city, add both indexes to the table.

Just keep in mind that every index you add increases the time it takes for MySQL to insert and update rows in the table.

-Rich

[Anon]

Thanks for taking taking the time to walk me through this.

If I intended to search by DNA, which would be a unique field, would I make it a primary field OR a unique/indexed field, i.e. is primary short hand for a unique AND indexed field? Can I have two primary fields in one table?

I am taking a look at the reference you provided now.
Thanks again,
Chris

[lawnmowerman]

Not a problem, Chris...always happy to help 🙂

In your example, my opinion is that it would be best to use the id column as the primary key and define it when you create your table like this:

id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)

and make dna a unique field and index by it:

dna INT,
UNIQUE dna (dna ),
INDEX table_dna (dna)

Of course, you'd replace "table" with your table name...
so, let's say you want to create a table called DNAdata, you could do it like this:

CREATE TABLE DNAdata (
id INT NOT NULL AUTO_INCREMENT,
name varchar(20),
city varchar(20),
state varchar(2),
dna INT,
PRIMARY KEY (id),
UNIQUE dna (dna),
INDEX DNAdata_dna (dna)
);

This table would then accept up to 20 characters for each of name and city and 2 characters for state.
Your primary key would be id and dna is indexed and unique.

From my SAMS book, here are the constraints on primary keys:

1) No two rows may have the same primary key value
2) Every row much have a not-null primary key value
3) The column containing primary key values can never
be modified or updated
4) Primary key values can be be reused (If a row is
deleted from the table, its primary key must
not be assinged to any new rows)

Item #1 says that primary keys must be unique

Item #2 says that a value must be assigned to the primary key, which is why auto_increment is so nice 🙂

Item #3 is the reason why data should not be used as
the primary key since you cannot update that column... which is another reason why auto_increment is so nice... the value is strictly arbitrary and has no relavence to the rest of the data in the row. If you used dna as the primary key, and a typo was made while inserting the data for that person, you could not go in and change it again. You would have to delete and re-insert the data instead of updating the existing row.

Item #4 means that, instead of not taking the time to think your updates through and skimping on code to delete and re-insert data, you should take the time to think things through, and write good code which knows the difference between an insert and an update...at least that is what it means to me 🙂

I'm sorry if I'm being too long winded, but database design determines how easily you can code the rest of your application and how efficient your database and PHP code will be. Do not make things harder than they have to be, but don't cut corners.

A improperly designed database will cause you many hours of hair pulling and probably quite a few thoughts of "Why didn't I do it another way from the start?"

If you take the time to thoroughly think every through when designing your database, you will not have to go through your code making changes every time you make a change to the database to fix something you should have made allowances for from the beginning. Sometimes database changes can't be avoided.... unforeseeable problems do arise, but proper database design will minimize these and maximize your productivity.

Again, sorry for being soooooo long winded...but I've taught myself many of these lessons the hard way 🙂

-Rich

[Anon]

I'll take a crack at this one if I may.

A primary key is NOT simply a unique index. No table can have more than one primary key. In a normalized database, every table must have a primary key.

A primary key also serves as a foreign key in related tables. I'll start with your example,

id | name | city | state | DNA code
1 joe woods PA 123
2 sam cave PA 456
3 jon cave PA 789

and add a second table, Pets:
pet id | owner id | name | species
1 1 Spot dog
2 1 Fluffy cat
3 2 Fido dog

In this example, owner id relates back to the id field in your table. ID and DNA code are both unique, but id is the primary key, so it's the one used in Pets.

You could use DNA code as the primary key, but the auto-increment 'id' field is a better choice because it will never change. Of course Joe will always have the same DNA code, but it could change in the database, for example if someone typed it in wrong and had to go back and correct it. ID has no meaning outside the database, so you'll never have to change it.

There's one more aspect to primary keys that's a little abstract, but essential for normalization: meaning. Every other field in a table must be related to the primary key. That makes DNA code kind of iffy as a primary key. (Is a person's name really related to their DNA code?) ID is an abstract identifier representing the person in question, so anything you can say about that person relates back to their ID.

[cmoore67]

So, in your pet example, the table would have only one primary key, i.e. pet id. Owner id would not be unique nor primary, but it may be indexed to speed queries, right?

Summary:
-only one primary key per table
-every table MUST have a primary key
-primary field values MUST be unique, but the UNIQUE command is not used when creating the field
-auto increment is almost the only way to assure unique values in the primary field
-a primary value should never change

(does auto increment alter the values of a primary field if a record in the middle of the table is deleted? If so, does that matter?)

I do not understand why, "every other field in a table must be related to the primary key". In your example, how is Fluffy related to 2, but cat not related to 2? or in my example, how is cave related to 2 but 456 not related to 2?

id | name | city | state | DNA code
1 | joe | woods| PA |123
2 | sam | cave | PA |456
3 | jon | cave | PA |789

Pets:
pet id | owner id | name | species
1 | 1 | Spot | dog
2 | 1 | Fluffy | cat
3 | 2 | Fido | dog

• unique fields are fields that can never have a duplicate value in two records
  -index fields are used to make queries run faster

Thanks for your help,
Chris

[cmoore67]

I appreciate the help. I am going to digest this and may get back to you.

Check my "summary" post in the next reply to see if I am getting it.

Thanks again,
Chris

[Anon]

You're right, Pet ID is the primary key for Pets. Having an index on owner id would speed up the query if you wanted to produce a report of people and their pets, something like this:
Joe's pets: Spot, Fluffy
Sam's pets: Fido

In this case, the query would use the owner id index to find the pet(s) belonging to each person.

I'm not sure, but I think the phrase "every other field in a table" was confusing. I meant every field except the key field(s). So a person's name, home city, home state and DNA code are all related to the person represented by the ID field.

Autoincrement fields keep the same value even if records are deleted. This is essential for related tables. For example, if deleting Joe changed Sam's ID to 1 and Jon's to 2, the pets table would show Sam having two pets, Spot and Fluffy, when in fact he only has one, Fido. That's why it's so important that primary key fields never change. Changing them breaks the links to other tables.

You've summarized the key points well. The only thing I'd change is "auto increment is almost the only way to assure unique values" to "auto increment is almost the only way to assure unique AND UNCHANGING values". In your table, DNA code would also assure uniqueness, but (as both Rich and I pointed out) only ID is guaranteed not to change.

[cmoore67]

Beth & Rich,
Thanks a lot, I think I've got it.
Chris

[lawnmowerman]

"-primary field values MUST be unique, but the UNIQUE command is not used when creating the field"

A primary field IS unique by definition, so it is implied, but I doubt it would hurt if you put it in there...just useless 🙂

"-auto increment is almost the only way to assure unique values in the primary field"

I have written monitoring software which uses a MAC address field as the primary key, since each and every MAC on each and every device must be unique. This worked fine, but just for the sake of proper design, when I rewrote the software, I added a new primary key field which was auto-incremented.

"(does auto increment alter the values of a primary field if a record in the middle of the table is deleted? If so, does that matter?)"

If a row is removed from a table which contains an auto-incremented primary key, that value is never reused...you will simply have a missing value if you list all of your auto-incremented primary keys:

SELECT id FROM tablename;

id

1
2
3

DELETE FROM tablename WHERE id=2;

SELECT id FROM tablename;

id

1
3

If I insert another record, the id field will be 4, and the next 5, etc etc....

I'm kinda confused by your question about relating the other fields to the primary key, so I'll leave that alone 🙂

-Rich

[lawnmowerman]

I don't 100% agree with this statement:

'The only thing I'd change is "auto increment is almost the only way to assure unique values" to "auto increment is almost the only way to assure unique AND UNCHANGING values"'

In the MySQL docs, there is nothing stating that auto-incrementing fields cannot have duplicated data.
In fact, the only way for the auto-increment option to take effect is to insert a NULL or 0 value for that field. If you specify a value other than NULL or 0, it will be used. At least that is what I get from reading:
http://www.mysql.com/doc/C/R/CREATE_TABLE.html

This is a good read especially since if you delete the last row from a table, depending on the table type, that last value may be reused. Also included is a line which states that if you delete all rows from a table, the auto-increment starts at 1 again. I read this as a warning 🙂

-Rich

[cmoore67]

Thanks Rich.

The final question was answered by the original author of the message.

One more question: do I have to search the unique and primary fields of my table and compare them to the data I intend to insert before I try to add the new data to the table?

For instance, if someone was trying to insert the same dna code for two different people, how would I avoid producing a system error?

Chris

[lawnmowerman]

That would be good practice. The constraints on the fields in the table are there to prevent data corruption...
well, at least corruption as far as you're expecting all the values to be unique and write your scripts as such, which would probably not return the expected data if values were duplicated.

It is good programming to have your script try to determine if there will be a problem before sending the data to the database. This includes making sure all required fields have values and that the values are of the correct format.

If you leave out data checking from your scripts, errors will be returned. Personally I would not rely on error messages as the primary means of branching in my script. I try to determine if the insert or update will fail.

An error message in this context tells you that your script has attempted to do something that it is not allowed to do. You should try to handle cases like this before an error is generated.

In your example, before inserting the new person, you could do a "SELECT dnacode FROM tablename WHERE dnacode = '01234'"
If any rows are returned, then the user is probably try to do an update with an insert OR trying to input duplicate data. At this point, you have trapped the error and can determine whether to return an error saying that the dna code already exists or you could change your INSERT statement to an UPDATE statement. In this case, I would probably tell the user that the dns code specified is already in the database and then ask the user if they wish to update the existing record or fix the data they have input.

-Rich

[Anon]

I don't understand what any of that has to do with an auto-increment field being unchanging. "Unchanging" doesn't mean "can't be changed" but "never needs to be changed".

Also, an auto-increment field doesn't have to enforce uniqueness. Making it a primary key does that. But if you're saying that it's possible to mess up an auto-increment field, then I totally agree.

[cmoore67]

perfect.
I think that does it.
Thanks a million!
Chris