SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Combination triple unique field?

    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    ALTER TABLE stats
    ADD UNIQUE ( pageid, yyyy, mm )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Or declare the combination as your primary key.
    why don't you wish it as the PK?

  4. #4
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John View Post
    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!

  5. #5
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    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...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy... for some reason I find the MySQL manual hard to read... I'll try harder next time

  8. #8
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Wow, removed 90k duplicates! Thanks for the advice guys... works like a charm.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •