CODEWITHSUNDEEP
mysql
andrew
andrew

Reputation: 9583

MySql replace with multiple primary keys

I have a table which has three primary keys and references three other tables enter image description here

Here is the table scheema:

CREATE TABLE IF NOT EXISTS training_matrix_reference(
  employee INT NOT NULL,
  training_matrix INT NOT NULL,
  training_record INT UNSIGNED NOT NULL,
  PRIMARY KEY (employee, training_matrix,training_record),
  FOREIGN KEY (employee) REFERENCES employees(id),
  FOREIGN KEY (training_matrix) REFERENCES training_matrix_courses(id),
  FOREIGN KEY (training_record) REFERENCES training_records(m_id)
)

I'm trying to craft a REPLACE statement which updates the training_record column or training_matrix column or both columns or creates a new row if not exists, but I also need to check that the employee belongs to the same company.

Here's what I tried so far:

REPLACE INTO `training_matrix_reference` 
  ( employee, training_matrix, training_record ) (
    SELECT id, '5', '100'
    FROM employees
    WHERE id =22
    AND company =64
    )

So my theory was that this should have replaced the first row in the table, updating training_record to 100 but in fact it actually created a new row:

                                         22      |           5 |           100

enter image description here

My guess is that this happened because training_record is a primary key?

But I'm not sure that removing the primary keys/references is the right way to go as this table is used as a many to many table in other queries.

Effectively what I'm trying to do is:

REPLACE INTO `training_matrix_reference` 
  ( employee, training_matrix, training_record ) 
  VALUES
  (22,33,18) 
  WHERE 
  employee = 22
  and training_matrix = 5  
  and training_record = 2189

But obviously a replace statement doesn't have a where clause.

I did check out these similar questions:

MySQL REPLACE INTO on multiple keys?

mysql REPLACE query with multiple primary keys

But unfortunately MySql is not my strong suit and I could really use some help.

I hope I explained things clearly, Thanks

Upvotes: 0

Views: 1144

Answers (2)

Talal Mujahed
Talal Mujahed

Reputation: 1

you should make a joining table between (employee, training_matrix_reference) or dispense at lest one relation

Upvotes: 0

spencer7593
spencer7593

Reputation: 108450

The PRIMARY KEY of the training_matrix_reference table is the combination of three columns. The table doesn't have multiple primary keys, it has a single PRIMARY KEY.

The REPLACE syntax you have is equivalent to performing:

DELETE FROM training_matrix_reference 
  WHERE employee        = 22
    AND training_matrix = 5
    AND training_record = 100
; 
INSERT INTO training_matrix_reference (employee, training_matrix, training_record)
VALUES (22, 5, 100);
;

The DELETE action only removes rows where the entire primary key is matched. Given the information you provided, we'd expect a row to be added to the table.

Did you have a question?

Upvotes: 2

Related Questions