mysql server wont accept id and datetime as a new record

Question

I have this table in which the primary key is the combination of an unsigned big int as an ID and then a datetime variable that is set as YYYY-MM-DD HH-MM-SS

i got the idea from the comments in: MySQL: Using DATETIME as primary key

The problem I am having is when I try to insert a new record with the same ID but different datetime, I am getting an error saying violation of the primary key constraint of duplicate IDs.

My guess is that MySQL is only comparing the YYYY-MM-DD part of the datetime data type since it's doing a string comparison, but I'm not sure... it's just my theory.

I was hoping someone who has used datetime as part of their primary key would have some insight.

the php code is right because obviously the first record is being created but a second record with the same ID is not getting created.

I was trying not to display my SQL code but here it is

    CREATE TABLE userPosts(
    postID      SERIAL          NOT NULL,
    username    varchar(21)     NOT NULL,
    time        datetime        NOT NULL,
    text        varchar(500),
    type        varchar(1)      NOT NULL    CHECK(type='t' OR type='f' OR type='u' OR type='b' OR type='c'),
    -- visits       integer         NOT NULL    DEFAULT 0,

    CONSTRAINT FK_users_userPosts   FOREIGN KEY(username)   REFERENCES users(username) ON DELETE CASCADE,
    CONSTRAINT PK_userPosts_username_postID_time    PRIMARY KEY(postID,time)
    );

I'm using a hierarchy model where this table would be described as the ISA table.

Wow, I think it's because I assigned ID as a SERIAL data type which I believe to be UNIQUE... I'll have to re-implement my php to auto increment ID's instead of the database doing it.

I'll re-implement my code and keep you guys posted.

If you add a primary key on both id and the datetime column, you can just make id be auto-increment. So even if 2 things are inserted less than 1 second apart (which will cause a duplicate datetime), it would still be unique because of the id. But then why even include the datetime in your primary key? Instead just make it a non-unique key on datetime and id as primary. — Mike, Aug 23 '13 at 05:42
Are you *sure* that the two dates for the same ID are, in fact, different? One recommendation: Remove the primary key and re-run the query. Then see what was put into the DB. Perhaps you are executing it twice for each one instead of once and you don't realize it. — Mike, Aug 23 '13 at 06:20
i had the php echo out the datetime to check for that exact reason, and they were different — nyduss, Aug 23 '13 at 06:25
What about the ID? Maybe that was the same as something else. — Mike, Aug 23 '13 at 06:26
that's the idea.. i want there to be multiple of the same id's with different time stamps. Think of it as the initial record is created at a particular time and you can continue to add to that record or ID at other timestamps. if the the actual post ever gets deleted you can delete all of the trailing records that correspond to it — nyduss, Aug 23 '13 at 06:30

score 0 · Answer 1 · answered Aug 23 '13 at 05:29

The error you describe means the date is simply not part of your primary key.

You should check how you created the table and the current schema to check if it's has been done correctly.

There is no reason for MySQL to compare on YYYY MM DD only, it will compare anything. If you've any doubt, it's sometimes easier to store your dates as Unix timestamp and rework them with PhP for your out puts, just think about it.

paxdiablo · Accepted Answer · 2013-08-23T09:09:35.747

No, MySql uses the entire datetime column as part of the key, assuming you have set it up correctly.

Therefore, it's likely you haven't set it up correctly. You would be advised to post your table specifications (or the create table command you used) so we can tell you where you went wrong.

In addition, you should show how you're inserting the data since that may be an issue as well (if, for example, it ignores the time portion because your insert is faulty).

And now that you've shown the create table command, at least one problem lies with the use of the serial data type. This is an alias for:

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

so is considered unique in its own right, regardless as to whether it's part of the primary key.

That means that no row can have the same value for postID even if it has a different time value. That won't be a problem if you're not explicitly setting that column or if you're setting it to 0 or null (since the auto-increment will kick in at that point), but it will bite you if you try to set an explicit value that already exists.

You should change that to something like BIGINT UNSIGNED NOT NULL if you want to explicitly set it and allow duplicates on that column. Then the primary key constraint will take care of duplicates across the combined postIDandtime` columns.

yeah you guys nailed it, I figured it out late last night. Thanks for the insight — nyduss, Aug 23 '13 at 16:08

score 0 · Answer 3 · answered Aug 23 '13 at 06:44

Just change the SERIAL TO BIGINT in create table query and it will work as per your expectation.

The problem is not with the time(datetime) field, the problem is with postId(SERIAL). When you use SERIAL, the column becomes auto increment. Since in your second query, you are passing one of the values for postID which already exists in the table, it is giving error.

mysql server wont accept id and datetime as a new record

3 Answers3