Avoid SQL INSERT duplicates for column value based on another column value

Viewed 58

Not sure if the question is quite clear so any recommendations are welcome.

I have following table:

Table1
----------
ID   fk_soc   fk_product   value
1    365      20           654896
2    365      21           654897
3    365      25           654898
4    820      20           114352
5    820      21           114381
6    820      25           114382

As you can see, in the second column, I can have same fk_product number set for different customer ID. But how can I avoid duplicate record for fk_product for same customer? I.e. avoid

Table1
----------
ID   fk_soc   fk_product   value
1    365      20           654896
2    365      20           whatever_here

If I set fk_product as unique, I won't be able to insert record for another customer. Records are inserted with normal SQL INSERT INTO and come from an HTML form:

INSERT INTO Table1 (fk_soc, fk_distributor_product_code, fk_product)
VALUES (
    ' . $this->db->escape($this->fk_line) . ',
    ' . $this->db->escape($this->value) . ',
    ' . $this->db->escape($this->fk_product) . '
)

I've tried using:

INSERT INTO Table1 (fk_soc, fk_distributor_product_code, fk_product)
VALUES (
    ' . $this->db->escape($this->fk_line) . ',
    ' . $this->db->escape($this->value) . ',
    ' . $this->db->escape($this->fk_product) . '
)
    WHERE NOT EXISTS (
        SELECT ' . $this->db->escape($this->fk_product) . '
        FROM table WHERE fk_soc= ' . $this->db->escape($this->fk_soc) . '

but this way no record is inserted

0 Answers
Related