Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.2k views
in Technique[技术] by (71.8m points)

mysql - 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) )
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The line:

FOREIGN KEY (pk_studentID ) REFERENCES student(pk_studentID ),

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 ;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...