CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
jbiz
jbiz

Reputation: 29

Primary Key Unique Constraint confusion / solution

I have a database with Employees, a Skill table and a Skillset table:

CREATE TABLE EMPLOYEE (
    EmployeeID NUMBER (5, 0) NOT NULL,
    *.....other fields*
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EmployeeID));


CREATE TABLE SKILL(
SkillID NUMBER (5, 0) NOT NULL,
Description VARCHAR2(30),
CONSTRAINT SKILL_PK PRIMARY KEY (SkillID),
CONSTRAINT SKILL_FK FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID));

CREATE TABLE SKILLSET(
    SkillSetID NUMBER (5,0) NOT NULL,
    EmployeeID NUMBER (5,0) NOT NULL,
    SkillID NUMBER (5,0) NOT NULL,
CONSTRAINT SKILLSET_PK PRIMARY KEY (SkillSetID)
CONSTRAINT SKILLSET_SKILL_FK FOREIGN KEY (SkillID) REFERENCES Skill(SkillID));
CONSTRAINT SKILLSET_EMP_FK FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID));

I have employees that can have multiple skills, and this is stored on the skillset table. I am getting ORA-00001: unique constraint (YSKILLSETID) violated when I attempt to assign more than one employee a skill from the Skill table.

This makes sense to me, and I'm still looking for a solution to allow multiple employees to have mutliple skills.

However, my confusion arises in that one employee can have many skills, shouldnt that be bound by the unique constraint to?

INSERT INTO SKILLSET VALUES (125, 31150, 2513);
INSERT INTO SKILLSET VALUES (124, 31150, 2523);
INSERT INTO SKILLSET VALUES (122, 31150, 8493);
INSERT INTO SKILLSET VALUES (121, 31153, 2141);
INSERT INTO SKILLSET VALUES (120, 31154, 2521);
INSERT INTO SKILLSET VALUES (119, 31154, 2141);
INSERT INTO SKILLSET VALUES (118, 31150, 2521);

screenshot of test

My questions:

Upvotes: 0

Views: 107

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29629

I think it would help to phrase the problem domain clearly. I think:

The system has many employees. The system has many skills. An employee may have zero or more skills. A skill belongs to zero or more employees.

If that's the case, you have the following tables:

Employee
----
ID (pk)
...

Skill
----
ID (pk)
Description 
...

Employee_skill
--------------
employee_id (fk)
skill_id (fk)
....

Upvotes: 1

Related Questions