SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    INSERT INGORE without unique key

    Hi everyone! I am new here and I have one simple question.

    I have table article tags with two fiels: page_id | tag
    For each tag new row is inserted. So the same pageId can be in more rows (for each tag new row) and also each tag can be in more than one row (for each pageId another row).

    But how can I avoid duplicates? Let's say user inserts 10 tags in one step.
    Code:
    INSERT IGNORE INTO tags (page_id, tag) VALUES (1,'tagname'),(1,'anothertag'),...
    won't work because page_id and tag can not be unique as the same page_id can be used for another tag.

    Do I actually need to make for 10 tags 20 queries? For each one first
    Code:
    $rows...SELECT COUNT(*) WHERE page_id=1 AND tag='tagname'
    if($rows!=1){INSERT...}
    Is this the way or how?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newbie85 View Post
    But how can I avoid duplicates?
    you're on the right track -- you have to use INSERT IGNORE

    but that won't work unless there is a key that contains the columns that you want checked for duplication

    ALTER TABLE tags
    ADD UNIQUE page_tag_ix (page_id, tag)

    simple, yes?

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

  3. #3
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    now it is, I didn't know I can use the same unique for two fields

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newbie85 View Post
    now it is, I didn't know I can use the same unique for two fields
    you can do the same with PRIMARY KEYs, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep, it's called a 'composite key' and ensures that where 'id' might be used several times in the table, it can still be unique because it is attached to the other column of the composite key which means

    1 | this
    1 | that
    1 | the other

    are each/all unique


    hth

    bazz


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
  •