0

I have a table that does not require a primary key. It consists of 4 columns which are email,date,time,message. Each time a user logs in, logs out, or does any particular action that is important, I log the email, date, time and action (message). Currently the table is setup with email as the Primary Key but I am unable to insert more than one record with the same email. I suppose I could use time as the PK but there is the possibility that two actions fall on the same time. How can I just use the table without a PK? I have tried turning it off for the email column but it does not allow me to.

emailcooke
  • 271
  • 1
  • 4
  • 17
  • 1
    Why not just use a standard `id INTEGER PRIMARY KEY` column? Using email as a primary key seems like a broken design, especially since you explicitly want multiples. – tadman Jun 03 '15 at 17:01
  • This table *does* require a primary key. You need to be able to uniquely identify any row in the table. If you don't want to use an `auto_increment` field (which is the suggested solution), you could add a timestamp and use that plus the email as a composite primary key (but that's not really a good idea). – gen_Eric Jun 03 '15 at 17:05

3 Answers3

1

Yes as you have defined email field as your primary, it can hold unique data only and no duplication allowed.

So you have two options:

1: Remove email field as a primary key

2: Add new integer field as a Primary key with auto increment (I would prefer this one)

Avinash
  • 6,064
  • 15
  • 62
  • 95
0

You could use a natural primary key that would be a combination of Email + Date + Time + Action. That combination would be unique. It is impossible for the same user to do 2 different actions at the same time. That will help you to keep integrity of your information.

Hope this helps you.

NMO
  • 293
  • 1
  • 10
  • Or you could use an artificial primary key like an numerical ID with auto_increment. It is a personal choice. Here is a interesting question: http://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys – NMO Jun 03 '15 at 17:14
  • Please note, that there's no guarantee for that combination to be unique, especially if the time resolution doesn't have much granularity: e.g. the same action from the same user can happen twice in the same second. – Oleg Komarov Jun 03 '15 at 20:21
0

To make a decision on a table' primary key one may start with considering these points (applicable to innodb):

  1. How the data is going to be accessed after it is written (if you don't query it, why store it?). If you care about read performance you should query your data by the primary key, since for innodb primary key is the only possible clustered index.

  2. The data is stored ordered by the primary key, so if you care about write performance, you should write data ideally ordered by your primary key, which always happens automatically if you have an auto_increment. Also table for which you don't explicitly specify a primary key are going to have a hidden auto_increment field which you won't be able to access, i.e. you get less for the same cost.

Oleg Komarov
  • 370
  • 1
  • 9