Page view table: I only want one row per ip per page, but can only have one primary key. How do I get around this?

Question

I have a page views table where every time a page is viewed, the user's ip and page id are put in a table. In this table, I only want one row for each ip/page id combination. If I set primary key to ip, then each ip can only be associated with one page. If I set primary key to page id, then only one ip will be associated with each page.

I need both of these to be keys because it's the combination of ip and page id that can not be repeated in the table. How can I do this?

$mysqli->query("INSERT INTO page_views (ip, page_id, view_key) VALUES ('$ip', '$page_id', '$view_key') 
ON DUPLICATE KEY UPDATE page_key='$view_key'");

This is how I need the setup to work - so every time someone views the page, they are assigned a view_key, and it is put in the table. If there is already a record of that ip viewing that page, then the view_key field should be overwritten.

Answer 1

If I understand correctly you need compound primary key:

CREATE TABLE(...,
PRIMARY KEY(page_id, ip));

Answer 2

Another option for you would be:

INSERT INTO your table (user_ip, page_id) SELECT * FROM (SELECT inserted_user_ip, inserted_page_id) AS tmp WHERE NOT EXISTS ( SELECT user_ip, page_id FROM your table WHERE user_id = inserted_user_id AND page_id = inserted_page_id ) LIMIT 1;

Answer 3

add a unique key to the table

ALTER TABLE table_name add unique key(ip_address,page_id);