INSERT INGORE without unique key

Databases
1

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.

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

$rows...SELECT COUNT(*) WHERE page_id=1 AND tag='tagname'
if($rows!=1){INSERT...}

Is this the way or how?

2

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?

:cool:

3

now it is, I didn’t know I can use the same unique for two fields :slight_smile:

4

you can do the same with PRIMARY KEYs, too :slight_smile:

5

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