sizgent_code需要是唯一的，但我不希望它能

Question

我有下表

CREATE TABLE assignment (
    assignment_code VARCHAR(10),
    course_code VARCHAR(10),
    PRIMARY KEY(course_code, assignment_code),
    FOREIGN KEY(course_code) REFERENCES course(course_code));

但是，当我尝试创建它时，postgresql告诉我tossment_code必须是唯一的。但是，我的任务是使每个课程独特地使sizgent_code代替完全唯一，我该怎么做？

下面的代码创建Enitre数据库（无数据）

CREATE TABLE students (
    student_id BIGINT PRIMARY KEY,
    first_name VARCHAR(15),
    surname VARCHAR(35),
    enrollment_year INT,
    bsn BIGINT,
    class VARCHAR(5),
    Graduated BOOLEAN);
CREATE TABLE teachers (
     bsn BIGINT PRIMARY KEY,
     first_name VARCHAR(15),
     surname VARCHAR(35),
     salary REAL,
     scale INT,
     CONSTRAINT salary CHECK(salary < (25 * scale) AND salary > (20 *     scale)));
CREATE TABLE course (
     course_code VARCHAR(10) PRIMARY KEY,
     course_name VARCHAR(20),
     course_follower VARCHAR(5),
     study_points INT,
     Amount_of_assignments INT);
CREATE TABLE teaches (
     bsn BIGINT,
     class VARCHAR(5),
     course_code VARCHAR(10),
     PRIMARY KEY(bsn, class, course_code),
     FOREIGN KEY(bsn) REFERENCES teachers(bsn),
     FOREIGN KEY(course_code) REFERENCES course(course_code));
CREATE TABLE study_program (
     course_name VARCHAR(20),
     course_code VARCHAR(10) PRIMARY KEY,
     level VARCHAR(15),
     duration VARCHAR(10),
     FOREIGN KEY(course_code) REFERENCES course(course_code));
CREATE TABLE assignment (
     assignment_code VARCHAR(10),
     course_code VARCHAR(10),
     PRIMARY KEY(course_code, assignment_code),
     FOREIGN KEY(course_code) REFERENCES course(course_code));
CREATE TABLE records (
     student_id BIGINT,
     course_code VARCHAR(20),
     school VARCHAR(50),
     PRIMARY KEY(student_id, course_code),
     FOREIGN KEY(student_id) REFERENCES students(student_id),
     FOREIGN KEY(course_code) REFERENCES study_program(course_code));
CREATE TABLE make (
     student_id BIGINT,
     assignment_code VARCHAR(20),
     completed BOOLEAN,
     PRIMARY KEY(student_id, assignment_code),
     FOREIGN KEY(student_id) REFERENCES students(student_id),
     FOREIGN KEY(assignment_code) REFERENCES assignment(assignment_code));
CREATE TABLE prerequisit (
    assignment_code VARCHAR(20),
    prerequisit_code VARCHAR(20),
    course_code VARCHAR(20),
    PRIMARY KEY(assignment_code, course_code),
    FOREIGN KEY(assignment_code) REFERENCES assignment(assignment_code),
    FOREIGN KEY(course_code) REFERENCES course(course_code),
    FOREIGN KEY(prerequisit_code) REFERENCES assignment(assignment_code));
CREATE TABLE records_2 (
    assignment_code VARCHAR(20),
    course_code VARCHAR(20),
    bsn BIGINT,
    mandatory BOOLEAN,
    year INT,
    week INT,
    PRIMARY KEY(assignment_code, course_code, bsn),
    FOREIGN KEY(assignment_code) REFERENCES assignment(assignment_code),
    FOREIGN KEY(course_code) REFERENCES course(course_code),
    FOREIGN KEY(bsn) REFERENCES teachers(bsn));    
CREATE TABLE designes (
    course_code VARCHAR(15),
    bsn BIGINT,
    PRIMARY KEY(course_code),
    FOREIGN KEY(course_code) REFERENCES course(course_code),
    FOREIGN KEY(bsn) REFERENCES teachers(bsn));    
CREATE TABLE reviews (
    bsn BIGINT,
    course_code VARCHAR(15),
    PRIMARY KEY(bsn, course_code),
    FOREIGN KEY(bsn) REFERENCES teachers(bsn),
    FOREIGN KEY(course_code) REFERENCES course(course_code));   

Answer 1

潜伏在您的代码深处是：

CREATE TABLE records_2 
    . . .
    FOREIGN KEY(assignment_code) REFERENCES assignment(assignment_code),

外键参考需要是唯一或主键。如果您有复合主键，则需要使用所有键。因此，此引用缺少course_code。

我是合成主要键的坚信 - 即提供此信息的串行整数列。我建议您在桌子中使用它们和外键参考。