Combination triple unique field?

[beley]

The stats system we built for one of our projects has a stats table which records every page view as a row of data. We run a daily script that produces a summary (for faster stats viewing) and inserts one row per "page" into a stats summary table.

The stats montly summary table thus has a pageid, year and month fields. Every day when we run the update, we delete that month's summary rows, then crunch the numbers and add them back. There should never be more than one row in the table with a combination of the three fields above...

Page 1 -- 12 -- 2008
Page 2 -- 12 -- 2008
Page 1 -- 11 -- 2008
Page 2 -- 11 -- 2008

Each of the three fields gets duplicated, but never should there be a duplicate combination of the three.

Is there a way to require this in MySQL (so it will error on insert) but NOT have it be the primary key?

Thanks!

[r937]

ALTER TABLE stats
ADD UNIQUE ( pageid, yyyy, mm )

[Dr John]

Or declare the combination as your primary key.
why don't you wish it as the PK?

[beley]

Or declare the combination as your primary key.
why don't you wish it as the PK?

We already have an ID PK and that table already has a few hundred thousand records. I guess we could change it, but if it wasn't necessary I figured why bother.

Thanks Rudy... I'll give it a shot!

[beley]

ALTER TABLE stats
ADD UNIQUE ( pageid, yyyy, mm )

One question before I change this -- what if there are duplicates already?

One of the reasons I'm adding is because we occasionally see a dupe make it in...

[r937]

the correct syntax is

ALTER [IGNORE] TABLE tablename
ADD UNIQUE ...

where IGNORE is an optional keyword

here's the relevant passage from da manual --

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table. If IGNORE isn't specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, then for rows with duplicates on a unique key, only the first row is used. The others are deleted.

you really should take some time to get to know da manual, it is your friend

[beley]

Thanks Rudy... for some reason I find the MySQL manual hard to read... I'll try harder next time

[beley]

Wow, removed 90k duplicates! Thanks for the advice guys... works like a charm.