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?

Solution

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.