如何将复合主键用作外键？看起来我的尝试不起作用.
create table student
(
student_id varchar (25) not null ,
student_name varchar (50) not null ,
student_pone int ,
student_CNIC varchar (50),
students_Email varchar (50),
srudents_address varchar(250),
dept_id varchar(6),
batch_id varchar(4),
FOREIGN KEY (dept_id) REFERENCES department(dept_id),
FOREIGN KEY (batch_id) REFERENCES batch(batch_id),
CONSTRAINT pk_studentID PRIMARY KEY (batch_id,dept_id,student_id) )
create table files
(
files_name varchar(50) not null ,
files_path varchar(50),
files_data varchar(max),
files_bookmarks xml ,
FOREIGN KEY (pk_studentID ) REFERENCES student(pk_studentID ),
CONSTRAINT pk_filesName PRIMARY KEY (files_name) )
解决方法:
这条线：
FOREIGN KEY (pk_studentID ) REFERENCES student(pk_studentID ),
是错的.你不能像这样使用pk_studentID,这只是父表中PK约束的名称.要将复合主键用作外键,您必须将具有相同数据类型的相同数量的列(组成PK)添加到子表,然后在FOREIGN KEY定义中使用这些列的组合：
CREATE TABLE files
(
files_name varchar(50) NOT NULL,
batch_id varchar(4) NOT NULL, --- added, these 3 should not
dept_id varchar(6) NOT NULL, --- necessarily be NOT NULL
student_id varchar (25) NOT NULL, ---
files_path varchar(50),
files_data varchar(max), --- varchar(max) ??
files_bookmarks xml, --- xml ??
--- your question is tagged MySQL,
--- and not SQL-Server
CONSTRAINT pk_filesName
PRIMARY KEY (files_name),
CONSTRAINT fk_student_files --- constraint name (optional)
FOREIGN KEY (batch_id, dept_id, student_id)
REFERENCES student (batch_id, dept_id, student_id)
) ENGINE = InnoDB ;
标签：sql,mysql,oracle,constraints
来源： https://codeday.me/bug/0923/1813714.html
