sql - 复合外键引用 2 个不同的表

Question

我创建了 3 个表Student，Department并且Major_in.

我想在 ( email, Department_ID)上创建一个复合外键，Major_in它引用 和的主键email。StudentDepartment_IDDepartment

CREATE TABLE Student
(
    email VARCHAR(20) PRIMARY KEY
);

CREATE TABLE Department
(
    Department_ID INTEGER PRIMARY KEY
);

CREATE TABLE Major_in 
(
    email VARCHAR(20), 
    Department_ID INTEGER,
    FOREIGN KEY (roll, Department_ID) 
         REFERENCES (Student(email), Department(Department_ID)
);

但我得到了错误

错误：“（”附近：语法错误insqlite3`

标签： sqlsqliteforeign-keysrelational-database

Answer 1

发现这样做是一个基本的语法错误。

CREATE TABLE Major_in
(
    email REFERENCES Student, 
    Department_ID REFERENCES Department, 
    PRIMARY KEY (email, Department_ID)
);