MySQL Primary Key doesn't cut it, what can I do?

Portal Home > Knowledgebase > Articles Database > MySQL Primary Key doesn't cut it, what can I do?

Posted by HoratioE, 06-10-2010, 08:40 PM
I'm making a logging script which logs four things, name, userid, ip and time. I can't really use a primary key with any of the fields except name but then if the client uses another account or his ip changes it won't be logged, similar problems would happen with any other field. What could I do to only prevent any new entries which the fields name, userid and ip are the same? Thank you
Posted by sbink, 06-10-2010, 09:32 PM
You should make your primary key a composite key consisting of (name,userid,ip).
Posted by robputt796, 06-14-2010, 12:51 PM
As sbink said with a composite key, however be careful a client might have a dynamic IP ;-). Last edited by robputt796; 06-14-2010 at 12:55 PM.
Posted by sramadan, 06-14-2010, 01:31 PM
Hey Horatio, I would consider the data structure here. I don't know what the purpose of the log is, but I usually keep a separate table with member information for most of my dbs. That way I can cross reference members with other key information in the db. For example, I would consider keeping a unique userid in a member table, cross-reference the userid to the log table and log all vars there when necessary. That way you won't need to worry about ip or name changes as the reference comes from a static source.
Posted by server prodigy, 06-14-2010, 03:24 PM
Create another field, a numeric "client ID" and format it as auto-incrementing. Then assign it as your primary key. This way each entry will be assigned a distinct identifier. Alternatives would be to use some part of the client's information as the number (last 4 digits of credit card, phone number, etc). Here's a "how-to": http://www.kavoir.com/2009/01/create...-in-mysql.html Last edited by server prodigy; 06-14-2010 at 03:30 PM.

Add to Favourites Print this Article