Multiple-column foreign key in MySQL?

by

Something like this ought to do it: 

CREATE TABLE MyReferencingTable AS (
   [COLUMN DEFINITIONS]
   refcol1 INT NOT NULL,
   rofcol2 INT NOT NULL,
   CONSTRAINT fk_mrt_ot FOREIGN KEY (refcol1, refcol2)
                        REFERENCES OtherTable(col1, col2)
) ENGINE=InnoDB;
  • MySQL requires foreign keys to be indexed, hence the index on the referencing columns
  • Use of the constraint syntax enables you to name a constraint, making it easier to alter and drop at a later time if needed.
  • InnoDB enforces foreign keys, MyISAM does not. (The syntax is parsed but ignored)

More Related Contents:

  1. Error related to only_full_group_by when executing a query in MySql
  2. How to declare a variable in MySQL?
  3. How do I escape special characters in MySQL?
  4. Enable remote MySQL connection: ERROR 1045 (28000): Access denied for user
  5. SQL counting all rows instead of counting individual rows
  6. MySQL – ERROR 1045 – Access denied
  7. Remote Connections Mysql Ubuntu
  8. ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘db’
  9. MySQL InnoDB not releasing disk space after deleting data rows from table
  10. Using SQL to determine word count stats of a text field
  11. Update one MySQL table with values from another
  12. Equivalent of varchar(max) in MySQL?
  13. How to escape literal percent sign when NO_BACKSLASH_ESCAPES option is enabled?
  14. MySQL Order before Group by
  15. How to transpose mysql table rows into columns
  16. MySQL incorrect key file for tmp table when making multiple joins
  17. “193: %1 is not a valid Win32 application” bug with a new Rails Application
  18. Does the order of conditions in a WHERE clause affect MySQL performance?
  19. How to display open transactions in MySQL
  20. Get total number of rows when using LIMIT? [duplicate]
  21. MySQL query, MAX() + GROUP BY
  22. Convert JSON array in MySQL to rows
  23. Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
  24. MySQL export into outfile : CSV escaping chars
  25. Simple Random Samples from a MySQL Sql database
  26. MySQL: Many tables or many databases?
  27. Select only last value using group by at mysql
  28. MySQL – Cannot insert NULL value in column, but I have a default value specified?
  29. How to escape apostrophe (a single quote) in MySql?
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment