CODEWITHSUNDEEP
mysqldatabase
law
law

Reputation: 23

MySQL DB duplicate unique keys

I was wondering is it possible to create a table that hold information on user logins using their id and game id that can only show up once a day.

So what I need is basically every time a user logs in it will record their id, game-id and their log in day and I want their id and game-id to be unique for that log in day, so the db will only log for example user 123 game A once on the 1-1-14 and wont log this again if the user logs in again on the same day. but will log it again on the 2-1-14, however can will log the user again on the 1-1-14 if it was User 1234 game B

sorry in advance if its not a good explanation or title =P

TABLE A
USERID GAMEID TIME
123     A     1-1-14
123     B     1-1-14
123     A     2-1-14

Upvotes: 0

Views: 76

Answers (3)

Nagaraj S
Nagaraj S

Reputation: 13484

SQL UNIQUE Constraint

  1. The UNIQUE constraint uniquely identifies each record in a database table.
  2. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
  3. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table

Upvotes: 0

lynndragon
lynndragon

Reputation: 370

CREATE table tbl (
USERID int,
GAMEID int,
primary key(USERID , GAMEID );

I think you wanna do composite keys..may be ok with this code

Upvotes: 0

Bilal Akil
Bilal Akil

Reputation: 4755

If you make the primary key all three fields (userid, gameid, time) then it won't allow you to insert what you call a duplicate. If making tem a primary key isn't an option, then just use unique.

CREATE TABLE A (
    userid ...,
    gameid ...,
    time ...,
    UNIQUE (userid, gameid, time)
);

That makes the combination of userid, gameid and time unique, or not allowing duplicates. If creating a new table isn't an allowable option then you can also use the following:

ALTER TABLE A ADD UNIQUE (userid, gameid, time)

... but you need to make sure there aren't any duplicates before running that.

Note that depending on whatever technologies you're using to send the SQL request, you may have to handle errors where your insertion will fail due to duplicates being present.

Upvotes: 1

Related Questions