"UNIQUE constraint failed". Unsure what I'm doing incorrectly

Question

I'm trying to set up 3NF tables and insert data, but keep getting 'UNIQUE constraint failed' error. Maybe I've done the 1NF-3NF conversion incorrectly, or incorrectly identified foreign/primary keys.

Insert method:

INSERT INTO Employee VALUES ('16', 'Smith'), ('33', 'Smith'),('30', 'Jenny'), ('16', 'Smith')

What I hope Employee table to look like (16-SMITH is supposed to duplicate):

EMP_ID	EMP_FNAME
16	SMITH
33	SMITH
30	JENNY
16	SMITH

My tables:

CREATE TABLE "Employee" (
    "EMP_ID"    INTEGER,
    "EMP_FNAME"    TEXT,
    PRIMARY KEY("EMP_ID")
);

CREATE TABLE "Project" (
    "PROJ_ID"    INTEGER,
    "JOB_TYPE"    INTEGER,
    "EMP_ID"    INTEGER,
    PRIMARY KEY("PROJ_ID","EMP_ID")
);

CREATE TABLE "HOURLY PAY" (
    "JOB_TYPE"    INTEGER,
    "HOUR_RATE"    INTEGER,
    PRIMARY KEY("JOB_TYPE"),
    FOREIGN KEY("JOB_TYPE") REFERENCES "Project"("JOB_TYPE")
);

Table I was provided with that I had to convert from 1NF->3NF:

PROJ_ID	PROJ_NAME	EMP_ID	EMP_FNAME	JOB_TYPE	HOUR_RATE
1135	Zulu	16	Smith	H25	20
1135	Zulu	33	Smith	H27	20
1188	Voyager	30	Jenny	H26	30
1188	Voyager	16	Smith	H25	20

https://stackoverflow.com/questions/29037793/sqlite-integrityerror-unique-constraint-failed — Martin, Oct 30 '22 at 14:29

Olivier Jacot-Descombes · Accepted Answer · 2022-10-30T15:49:38.533

Since EMP_ID is the primary key. It must be unique. The primary key allows you to uniquely identify one record. Assuming that you could insert the Id 16 twice and later wanted to delete one of these two records, how would you accomplish it? DELETE FROM Employee WHERE EMP_ID = 16 would delete both of them.

This is the very point of normalization. Every entity must be unique and information must not be repeated.

You need one more table I think. You need a Project table with a unique Project ID and a project name. Then you need a junction table between Project and Employee. We could call it "Assignment". It must have the structure of the table you are calling "Project" now.

CREATE TABLE Employee (
    EMP_ID     INTEGER,
    EMP_FNAME  TEXT,
    PRIMARY KEY (EMP_ID)
);

CREATE TABLE Project (
    PROJ_ID      INTEGER,
    PROJ_NAME    TEXT,
    PRIMARY KEY (PROJ_ID)
);

CREATE TABLE JobType (
    JOB_TYPE     TEXT,
    HOUR_RATE    INTEGER,
    PRIMARY KEY (JOB_TYPE)
);

CREATE TABLE Assignment (
    PROJ_ID      INTEGER,
    EMP_ID       INTEGER,
    JOB_TYPE     TEXT,
    PRIMARY KEY (PROJ_ID, EMP_ID),
    FOREIGN KEY (PROJ_ID) REFERENCES Project (PROJ_ID),
    FOREIGN KEY (EMP_ID) REFERENCES Employee (EMP_ID),
    FOREIGN KEY (JOB_TYPE) REFERENCES JobType (JOB_TYPE)
);

Employees, Projects and JobTypes are unique. In "Assignment" Employee/Project pairings are unique.

INSERT INTO Employee (EMP_ID, EMP_FNAME) VALUES
  (16, 'Smith'),
  (30, 'Jenny'),
  (33, 'Smith');

INSERT INTO Project (PROJ_ID, PROJ_NAME) VALUES
  (1135, 'Zulu'),
  (1188, 'Voyager');

INSERT INTO JobType (JOB_TYPE, HOUR_RATE) VALUES
  ('H25', 20),
  ('H26', 30),
  ('H27', 20);

INSERT INTO Assignment (PROJ_ID, EMP_ID, JOB_TYPE) VALUES
  (1135, 16, 'H25'),   <== Smith 16
  (1135, 33, 'H27'),
  (1188, 30, 'H26'),
  (1188, 16, 'H25');   <== Smith 16 again, but assigned to a different project.

One would have to consider whether it would not be better to assign a neutral numeric primary key to the job type. The job code (like 'H25') would be a separate column. Meaningful primary keys can be problematic. If someone wants to introduce another naming scheme for the job types, this is difficult to realize when the name is the primary key.

You can then re-create the information as contained in the original table with a query

SELECT
   a.PROJ_ID,
   p.PROJ_NAME,
   a.EMP_ID,
   e.EMP_FNAME,
   a.JOB_TYPE,
   j.HOUR_RATE
FROM
    Assignemnt a
    INNER JOIN Employee e ON a.EMP_ID = e.EMP_ID
    INNER JOIN Project p ON a.PROJ_ID = p.PROJ_ID
    INNER JOIN JobType j ON a.JOB_TYPE = j.JOB_TYPE
ORDER BY
    a.PROJ_ID,
    e.EMP_FNAME

See: http://sqlfiddle.com/#!5/3dcdb/3/0

score 0 · Answer 2 · answered Oct 30 '22 at 14:29

0

INSERT INTO Employee VALUES ('16', 'Smith'), ('33', 'Smith'),('30', 'Jenny'), ('16', 'Smith')

You have two Smith, and both of their ids are 16.

answered Oct 30 '22 at 14:29

kitman0000

11
2

Yes, that's how it's supposed to be. I'm having issues inserting them in two separate rows. In the original table (at the bottom of my question) they are different. One is with the 'Zulu' project, and the other with 'Voyager' – ara Oct 30 '22 at 14:34
@ara Tow 'Smith's have same id. Id is the primary key in your table, it can't be duplicate, if you have two people called Smith, use two different IDs. If you want Smith to join 2 projects, this is a many to many relationship, so you need another table to record it. If you want Smith to lead 2 projects, this is a one to many relationship, you can add a field in Project table, which should be 'LEADER_ID'. – kitman0000 Oct 30 '22 at 15:23

"UNIQUE constraint failed". Unsure what I'm doing incorrectly

2 Answers2