存在主键却提示"No Unique Constraint Matching Given Keys"的SQL问题咨询

First, let's break down what's causing this error and fix it step by step. Looking at your code snippet, the primary key definition for the class_offerings table is cut off:

primary key (class_code, se...

This incomplete declaration is the core issue—your database can't recognize a valid, complete unique constraint for the primary key you intended to set up.

Here are the key fixes and improvements to resolve this:

• Complete the composite primary key: Since class_offerings represents a specific run of a class in a semester, the logical unique identifier is a composite of class_code and semester_code. This ensures no duplicate offerings for the same course in the same semester.
• Explicitly mark primary key fields as non-null: While most databases automatically treat primary key fields as non-null, declaring this explicitly avoids edge cases and makes your schema clearer.
• Add a foreign key constraint: Link class_code in class_offerings to the classes table's primary key—this enforces referential integrity, so you can't create an offering for a course that doesn't exist.

Corrected class_offerings Table Creation Code #

create table class_offerings( --A particular instance of a class
    class_code varchar(10) not null,
    class_name varchar(128) not null,
    semester_code integer not null,
    maximum_capacity integer check (maximum_capacity >= 0),
    teacher_name varchar(50) not null,
    primary key (class_code, semester_code),
    foreign key (class_code) references classes(class_code)
);

Why This Works: #

1. The complete (class_code, semester_code) composite primary key creates a valid unique constraint the database can recognize, eliminating the error message.
2. Explicit NOT NULL declarations ensure there are no null values in the primary key fields, which would break the unique constraint.
3. The foreign key constraint keeps your classes and class_offerings tables consistent, a fundamental requirement for relational databases.

If you still hit issues after applying this, double-check:

• That the classes table was created successfully before class_offerings (since we're referencing it).
• That there are no existing duplicate (class_code, semester_code) pairs if you're adding this constraint to an already populated table (you'll need to clean up duplicates first).

内容的提问来源于stack exchange，提问作者MMMMMCK