2

PostgreSQL/PGAdmin4 ERROR: there is no unique constraint matching given keys for referenced table

This is the ‘schema’ I’m trying to code into PGAdmin4/Postgresql: https://i.sstatic.net/PWjSO.jpg

I was able to convert all tables, except “QUALIFICATIONS”.

I tried to process the following query:

create table REGISTRATION(
    StudentID int,
    SectionNo int,
    Semester varchar(16),
    foreign key(StudentID) references Student(StudentID),
    foreign key (SectionNo, Semester) references Section(SectionNo, Semester),
    primary key(studentID, SectionNo, Semester)
    );

I received the following message: ERROR: there is no unique constraint matching given keys for referenced table "section"

These are the foreign and primary I have in the table SECTION

PKEY: i.imgur.com/BcUNKug.jpg

FKEY: i.imgur.com/D8B8hRW.jpg

Code of SECTION table:

CREATE TABLE class_scheduling_01.section
(
sectionno integer NOT NULL,
semester character varying(16) COLLATE pg_catalog."default" NOT NULL,
courseid character varying(16) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT section_pkey PRIMARY KEY (sectionno, semester, courseid),
CONSTRAINT section_sectionno_key UNIQUE (sectionno),
CONSTRAINT section_courseid_fkey FOREIGN KEY (courseid)
    REFERENCES class_scheduling_01.course (courseid) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)

I additionally ran the command: ALTER TABLE section ADD UNIQUE (sectionno);

Since none of the attributes seemed to be repeating itself.

Despite all this I’m getting: ERROR: there is no unique constraint matching given keys for referenced table "section" Query returned successfully in 642 msec.

Edit: I've gone back to the COURSE table and made courseID a unique constraint. I still get the same message. SECTION table has a composite primary key made up of 3 columns. As seen in the first picture linked, out of all the values, only SECTION.sectionno is the only column with unique/non-repeating values.

2nd edit: I decided to create the table "REGISTRATION" one step at a time, and make the foreign keys last with alter table command.

I was able to make the columns StudentID andd SectionNo foreign keys to their respective columns. When I tried to make REGISTRATION.semester a foreign key to SECTION.semester I got the error message again. alter table REGISTRATION add foreign key (semester) references section(semester);

As seen in the image I linked Semester value, is repeated; despite this, am I still required to make it unique? Or do I make a unique command assigning all 3 columns (of SECTION) together as unique, instead of just 1? If so, how?

Improve this question
5
  • 4
    meta.stackoverflow.com/questions/285551/… Commented Oct 31, 2016 at 14:25
  • Is there a way for me to generate a code of the entire schema without having to go through each indvidual tables (5 of 6 so far), and doing Script → 'create script'? Commented Oct 31, 2016 at 15:09
  • The table referenced in your first script is called Section, but the actual table starts with a lower case "s": section. Best to keep everything lowercase in Postgresql. Your columns are also incorrectly cased based on the actual column definitions. Commented Oct 31, 2016 at 15:17
  • SQL is case insensitive. I wrote it that that way to make it easier to spot typos. Commented Oct 31, 2016 at 16:12
  • It would be better to keep the casing the same. You will be prone to error that way. Commented Oct 31, 2016 at 16:15

1 Answer 1

Reset to default
1

This

foreign key (SectionNo, Semester) references Section(SectionNo, Semester),

requires that there be a unique constraint on the pair of columns SectionNo and Semester.

CREATE TABLE class_scheduling_01.section
(
sectionno integer NOT NULL,
semester character varying(16) COLLATE pg_catalog."default" NOT NULL,
courseid character varying(16) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT section_pkey PRIMARY KEY (sectionno, semester, courseid),
CONSTRAINT section_sectionno_key UNIQUE (sectionno),
CONSTRAINT section_courseid_fkey FOREIGN KEY (courseid)
    REFERENCES class_scheduling_01.course (courseid) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
-- Unique constraint on the pair
CONSTRAINT your_constraint_name UNIQUE (SectionNo, Semester)
);

That change should let these SQL statements succeed. I didn't check to see whether that's a good idea.

SQL is case insensitive.

I understand what you mean, but this is a bad way to think about it.

PostgreSQL folds unquoted identifiers to lowercase. So PostgreSQL would treat the identifiers SQL, Sql, and sql as if they were all sql. A quoted or delimited identifier, like "Select" always refers to either a table or a column; it's never interpreted as a keyword. Quoted identifiers are case-sensitive. You can't successfully refer to the table "Select" as select.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.