Use Composite Primary Key as Foreign Key

by

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 ;

More Related Contents:

  1. In SQL Server how to get the count of child if both parent and child are available [closed]
  2. How to select the nth row in a SQL database table?
  3. SQL: Repeat a result row multiple times, and number the rows
  4. How do I add a custom CHECK constraint on a MySQL table?
  5. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  6. Does mysql have the equivalent of Oracle’s “analytic functions”?
  7. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  8. What does SQL Select symbol || mean?
  9. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  10. Why in SQL NULL can’t match with NULL?
  11. how to delete data from table A besides the customer service and director data in table B?
  12. SQL split values to multiple rows
  13. Get records with max value for each group of grouped SQL results
  14. How to populate a table with a range of dates?
  15. MYSQL OR vs IN performance
  16. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  17. Convert timestamp to date in MySQL query
  18. Update a column value, replacing part of a string
  19. MySQL: View with Subquery in the FROM Clause Limitation
  20. MySql export schema without data
  21. MySQL combine two columns into one column
  22. MySQL Insert Into from one Database in another
  23. MySQL: LIMIT by a percentage of the amount of records?
  24. limiting the rows to where the sum a column equals a certain value in MySQL
  25. MySQL multiple columns in IN clause
  26. MySQL Inner Join Query Multiple Tables
  27. GROUP BY – do not group NULL
  28. Composite key as foreign key (sql)
  29. MySQL 1062 – Duplicate entry ‘0’ for key ‘PRIMARY’
  30. MySQL LEFT JOIN 3 tables

Leave a Comment