使用复合主键作为外键 [英] Use Composite Primary Key as Foreign Key

问题描述

如何使用复合主键作为外键?看来我的尝试无效.

How can I use a composite primary key as a foreign key? It looks like my attempt does not work.

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定义中使用这些列的组合:

is wrong. You can't use pk_studentID like that, this is just the name of the PK constraint in the parent table. To use a compound Primary Key as Foreign Key, you'll have to add the same number of columns (that compose the PK) with same datatypes to the child table and then use the combination of these columns in the FOREIGN KEY definition:

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 ;

这篇关于使用复合主键作为外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！