What is difference between foreign key and reference key?

by

I am supposing that you are talking about using the REFERENCES where the FOREIGN KEY keyword is not used when constraining a column inline, which is called a column-level foreign key constraint, eg.

author_id INTEGER REFERENCES author(id)

… instead of the table-level foreign key constraint, which is placed after the column declarations …

author_id INTEGER,
FOREIGN KEY(author_id) REFERENCES author(id)

The answer is, that it is simply shorthand syntax for the same thing. The main concern when altering between the two should be readability.

For more advanced use, it might be relevant that only table-level foreign key constraints can describe constraints on multiple keys at once, where all must be present in the referenced table.

Do note that MySQL ‘parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification’, meaning that only the table-level foreign key constraint will work.

Both Postgres and Microsoft’s SQL Server respect both column- and table-level foreign key constraints.

More Related Contents:

  1. Can table columns with a Foreign Key be NULL?
  2. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  3. Why are foreign keys more used in theory than in practice?
  4. SQL query for multiple rows in single column [closed]
  5. Why is SELECT * considered harmful?
  6. Solutions for INSERT OR UPDATE on SQL Server
  7. MySQL query finding values in a comma separated string
  8. How to check if a table exists in a given schema
  9. How do I reset a sequence in Oracle?
  10. ORA-00904: invalid identifier
  11. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  12. How to update selected rows with values from a CSV file in Postgres?
  13. How to create multiple one to one’s
  14. How to select rows with no matching entry in another table?
  15. Strings as Primary Keys in SQL Database [closed]
  16. How to select unique records by SQL
  17. Fastest way to count exact number of rows in a very large table?
  18. Key value pairs in relational database
  19. T-SQL Cast versus Convert
  20. Deleting duplicate rows from sqlite database
  21. Decision between storing lookup table id’s or pure data
  22. How Can I use “Date” Datatype in sql server?
  23. How to replace a string in a SQL Server Table Column
  24. How many rows in a database are TOO MANY?
  25. SQL – How to transpose?
  26. Primary key or Unique index?
  27. How to get a list of column names on Sqlite3 database?
  28. How to run a stored procedure in sql server every hour?
  29. Splitting the string in sql server
  30. Composite Primary key vs additional “ID” column?

Leave a Comment