Failed to add the foreign key constraint 2

CREATE TABLE Program(
    name VARCHAR(64) NOT NULL,
    abbreviation VARCHAR(16) NOT NULL,
    PRIMARY KEY (name)
);

​x
 
CREATE TABLE Program(    name VARCHAR(64) NOT NULL,    abbreviation VARCHAR(16) NOT NULL,    PRIMARY KEY (name));​

CREATE TABLE Student(
    ssn INTEGER CHECK (ssn > 0) NOT NULL,
    name VARCHAR(64) NOT NULL,
    login VARCHAR(16) NOT NULL,
    program VARCHAR(64) NOT NULL,
    PRIMARY KEY (ssn),
    FOREIGN KEY (program) REFERENCES Program(name),
    UNIQUE (login),
    UNIQUE (ssn, program)
);

 
CREATE TABLE Student(    ssn INTEGER CHECK (ssn > 0) NOT NULL,    name VARCHAR(64) NOT NULL,    login VARCHAR(16) NOT NULL,    program VARCHAR(64) NOT NULL,    PRIMARY KEY (ssn),    FOREIGN KEY (program) REFERENCES Program(name),    UNIQUE (login),    UNIQUE (ssn, program));​

CREATE TABLE Branch(
    name VARCHAR(64) NOT NULL,
    program VARCHAR(64) NOT NULL,
    PRIMARY KEY (name),
    FOREIGN KEY (program) REFERENCES Program(name)
);

 
CREATE TABLE Branch(    name VARCHAR(64) NOT NULL,    program VARCHAR(64) NOT NULL,    PRIMARY KEY (name),    FOREIGN KEY (program) REFERENCES Program(name));​

CREATE TABLE BelongsTo(
    student INTEGER NOT NULL,
    branch VARCHAR(64) NOT NULL,
    program VARCHAR(64) NOT NULL,
    FOREIGN KEY (student, program) REFERENCES Student(ssn, program),
    FOREIGN KEY (branch, program) REFERENCES Branch(name, program)
);

 
CREATE TABLE BelongsTo(    student INTEGER NOT NULL,    branch VARCHAR(64) NOT NULL,    program VARCHAR(64) NOT NULL,    FOREIGN KEY (student, program) REFERENCES Student(ssn, program),    FOREIGN KEY (branch, program) REFERENCES Branch(name, program));​

Program, Student, Branch got successfully created. But while creating BelongsTo sql throws:

Failed to add the foreign key constraint. Missing index for constraint ‘belongsto_ibfk_2’ in the referenced table ‘branch’

Also, I need to design schema as per: http://www.cse.chalmers.se/edu/year/2018/course/TDA357/VT2018/lab/task1.txt

Please feel free to ask me about more details regarding this problem, thanks in advance.

Answer

You don’t need program for the branch foreign key or for the student foreign key:

CREATE TABLE BelongsTo(
    student INTEGER NOT NULL,
    branch VARCHAR(64) NOT NULL,
    program VARCHAR(64) NOT NULL,
    FOREIGN KEY (student, program) REFERENCES Student(ssn),
    FOREIGN KEY (branch) REFERENCES Branch(name)
);

 
CREATE TABLE BelongsTo(    student INTEGER NOT NULL,    branch VARCHAR(64) NOT NULL,    program VARCHAR(64) NOT NULL,    FOREIGN KEY (student, program) REFERENCES Student(ssn),    FOREIGN KEY (branch) REFERENCES Branch(name));​

Note: The student foreign key definition works because you have a redundant unique constraint on student(ssn, program). The column ssn is already unique, because it is the primary key, so program is always unique per student too (because there is only one of them).

Advertisement

Answer