CODEWITHSUNDEEP
mysqlsql
Markus
Markus

Reputation: 3782

How to restrict inserting duplicate pairs of values in a table?

I have the following table in MySQL DB (MariaDB):

CREATE TABLE `restrictions_projects` (
  `ID` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `restriction_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `restrictions_projects`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `project_id` (`project_id`),
  ADD KEY `restriction_id` (`restriction_id`);

ALTER TABLE `restrictions_projects`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `restrictions_projects`
  ADD CONSTRAINT `restrictions_prfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`ID`),
  ADD CONSTRAINT `restrictions_prfk_2` FOREIGN KEY (`restriction_id`) REFERENCES `restrictions` (`ID`);
COMMIT;

How can I restrict inserting duplicate pairs of project_id and restriction_id?

INSERT INTO restrictions_projects (project_id,restriction_id) VALUES (1,2) // ??? ON DUPLICATE KEY UPDATE project_id=1 AND restriction_id=2

Upvotes: 0

Views: 66

Answers (2)

Ravi
Ravi

Reputation: 31417

You need composite unique key on both column

UNIQUE (project_id,restriction_id)

You should alter your table to add unique key

ALTER TABLE restrictions_projects ADD CONSTRAINT restrictions_projects_uniq UNIQUE (project_id,restriction_id)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Add a unique constraint:

ALTER TABLE restrictions_projects
  ADD CONSTRAINT unq_restrictions_2 UNIQUE(project_id, restriction_id);

You can also do this using a unique index, which is effectively the same thing.

Upvotes: 1

Related Questions