Merge statement into table creates duplicates?

Question

I have an Education table as specified here, which references a Programs and Users table.

The important parts of those two tables are just the keys program_id and user_id.

CREATE TABLE Education(
    user_id INTEGER NOT NULL,
    program_id INTEGER NOT NULL,
    FOREIGN KEY (program_id) REFERENCES Programs,
    PRIMARY KEY (user_id),
    FOREIGN KEY(user_id) REFERENCES Users,
    program_year INTEGER NOT NULL
);

I try to merge a different table into this one like so:

MERGE INTO Education e
    USING (SELECT u.user_id, p.program_id, u.program_year
           FROM project1.Public_User_Information u,
                Programs p
           WHERE p.institution = u.institution_name
             AND p.concentration = u.program_concentration
             AND p.degree = u.program_degree
    ) d
    ON (e.user_id = d.user_id)
    WHEN NOT MATCHED THEN
        INSERT (user_id, program_id, program_year)
        VALUES (d.user_id, d.program_id, d.program_year);

The idea with the WHERE statement is to get a program_id from Programs that matches the corresponding institution name, concentration, and program degree.

I then check for a duplicate key user_id, and if it's not matched, I insert a new entry.

However, I keep getting the error that a unique constraint was violated with this merge.

Am I doing anything wrong? Thank you so much!

Answer 1

Make sure that your SELECT statement does not return multiple rows for each user_id.

For example:

CREATE TABLE programs (
  program_id    INTEGER PRIMARY KEY,
  institution   VARCHAR2(20),
  concentration VARCHAR2(20),
  degree        VARCHAR2(20)
);

CREATE TABLE users (
  user_id               INTEGER PRIMARY KEY
);

CREATE TABLE /*project1.*/Public_User_Information (
  user_id               INTEGER,
  program_year          INTEGER,
  institution_name      VARCHAR2(20),
  program_concentration VARCHAR2(20),
  program_degree        VARCHAR2(20)
);

CREATE TABLE Education(
    user_id INTEGER NOT NULL,
    program_id INTEGER NOT NULL,
    FOREIGN KEY (program_id) REFERENCES Programs,
    PRIMARY KEY (user_id),
    FOREIGN KEY(user_id) REFERENCES Users,
    program_year INTEGER NOT NULL
);

INSERT ALL
  INTO programs (program_id, institution, concentration, degree)
    VALUES (1, 'A', 'B', 'C')
  INTO users (user_id) VALUES (1)
  INTO /*project1.*/Public_User_Information (user_id, program_year, institution_name, program_concentration, program_degree)
    VALUES (1, 1970, 'A', 'B', 'C')
  INTO /*project1.*/Public_User_Information (user_id, program_year, institution_name, program_concentration, program_degree)
    VALUES (1, 1971, 'A', 'B', 'C')
SELECT * FROM DUAL;

Then:

MERGE INTO Education e
    USING (SELECT u.user_id, p.program_id, u.program_year
           FROM /*project1.*/Public_User_Information u,
                Programs p
           WHERE p.institution = u.institution_name
             AND p.concentration = u.program_concentration
             AND p.degree = u.program_degree
    ) d
    ON (e.user_id = d.user_id)
    WHEN NOT MATCHED THEN
        INSERT (user_id, program_id, program_year)
        VALUES (d.user_id, d.program_id, d.program_year);

Outputs:

ORA-00001: unique constraint (FIDDLE_MPWPWTLRQFOVUMVRIBPO.SYS_C00104416) violated

as:

SELECT u.user_id, p.program_id, u.program_year
FROM   /*project1.*/Public_User_Information u,
       Programs p
WHERE  p.institution   = u.institution_name
AND    p.concentration = u.program_concentration
AND    p.degree        = u.program_degree

Outputs multiple rows for the same user_id:

USER_ID	PROGRAM_ID	PROGRAM_YEAR
1	1	1970
1	1	1971

fiddle