CODEWITHSUNDEEP
mysqlsql-updatesql-deletecomposite-primary-key
Gamma
Gamma

Reputation: 347

How to use composite primary key for deletion

I'm new to the database and my question is how can I use composite primary key for deletion. here is my table structure,

CREATE TABLE DevProj (
    DeveloperID int NOT NULL,
    ProjectID int NOT NULL,
    WorkDate Datetime,
    WorkingHours int,
    Overtime int,
    Descriptions varchar(255),
    Primary key(DeveloperID,ProjectID,WorkDate) 
);

insert into DevProj (DeveloperID,ProjectID,WorkDate,WorkingHours,Overtime,Descriptions) values
(1,2,'2018-1-1',10,5,'done all'),
(1,2,'2018-1-2',10,5,'done C++'),
(1,3,'2018-1-1',10,5,'done C#'),
(2,2,'2018-1-3',10,5,'done JAVA'),
(3,3,'2018-1-4',10,5,'done SQL');

suppose I need to delete (1,2,'2018-1-1',10,5,'done all') this record, how can I do that, there is composite priamry key and no individual column as a primary key to delete like this, Ex: Delete from devProj where id = 1 .So then, how can I write query for delete record, I faced this problem when the updation also.

Upvotes: 2

Views: 8781

Answers (2)

Nicolas C.
Nicolas C.

Reputation: 598

Alternatively, to delete several records:

DELETE FROM DevProj 
WHERE (DeveloperID, ProjectID, WorkDate) IN ((1,2,'2018-1-1'), ((1,2,'2018-1-2'))

Additional performance considerations

But please note that if you have a MySQL version lower than 5.7.11, this will not take advantage of the index optimization (see https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-optimization). In such case, you should use AND/OR operators:

DELETE FROM DevProj 
WHERE (DeveloperID = 1 AND ProjectID = 2 AND WorkDate = '2018-1-1') 
OR (DeveloperID = 1 AND ProjectID = 2 AND WorkDate = '2018-1-2')

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522331

If you want to delete a record based on the composite primary key, you could use MySQL's shorthand:

DELETE
FROM DevProj
WHERE (DeveloperID, ProjectID, WorkDate) = (1, 2, '2018-01-01');

Or you could use the longer version:

DELETE
FROM DevProj
WHERE
    DeveloperID = 1 AND
    ProjectID = 2 AND
    WorkDate) = '2018-01-01';

Upvotes: 2

Related Questions