How does wordpress link columns from different tables without using FOREIGN KEY or JOIN?

It doesn’t. WordPress doesn’t use foreign keys to link tables together that way. There are also lots and lots of other reasons to declare keys and primary keys in a table that have nothing to do with this type of linkage/referencing. For example telling MySQL/MariaDB the ID column is a primary key give critical clues and hints to how it should store and index that table for performance.

For a custom plugin I want to recreate the terms, taxonomy and relationship tables from wordpress in my own tables But WordPress do not use FOREIGN KEY, only ‘UNIQUE KEY’ and ‘KEY’ in sql syntaxe.

I do not see why FOREIGN KEY is needed for this, or why you need to create new tables.

Any “object” can have a taxonomy, not just posts. User taxonomies are a thing for example. The requirement though is that all objects in a taxonomy are the same type, so you can’t mix users and posts as it has no way of knowing if the ID 5 is for the user 5 or the post 5.

So register a taxonomy then pass it the IDs of your custom things, not post IDs. Don’t create your own tables, this will add an enormous amount of technical debt and additional work, while preventing compatibility with most of the tools available.

How Does WP query for posts terms meta etc together?

Using SQL queries and JOIN statements constructed in PHP.

A post meta table entry has a column containing the ID of the post. When WordPress wants to fetch all meta for a post it fetches those rows that have the matching ID using a SELECT statement in a follow up query, or a JOIN. WP_Query, WP_Term_Query, WP_User_Query etc are the relevant classes.

Keep in mind that WordPress does not use or implement an ORM database system, and predates many of the big PHP ORM database libraries such as Doctrine. Objects such as WP_Post are just containers for data and can’t be used to delete/update/create.

You also shouldn’t be constructing your own database tables in WordPress routinely. Custom database tables are possible but there is almost no UI support for them, and no API support for accessing them beyond raw SQL.

For example when querying it might use JOIN to select only the post meta with an ID that matches the post, but this is all done in PHP in the application layer. The database is unaware that the post ID 5 in the post meta tables post ID column refers to the ID of a row in the posts table.

Note that the columns that store these IDs are just that, columns that store a number that represents an ID. There is no magic to tie it to a row in another table automatically, no links. You can create rows in the post meta table for posts that don’t exist for example, nothing on the database side enforces that it must have the ID of a post that exists.

This also means if you delete a post with raw SQL it won’t delete the term associations or post meta, those are all done in PHP triggering separate DB queries.

Related Posts:

  1. How to use $wpdb to delete in a custom table
  2. get_results on large datasets
  3. Is there a (better) way to access $wpdb results?
  4. Using $wpdb generates DB error
  5. How do you use prepare when asking for a list of id’s
  6. how to execute different sql query in non-sanitized $wpdb->get_results function
  7. WPDB update row with != in where clause
  8. How to update records using $wpdb?
  9. Need help writing a $wpdb query
  10. show badge with count for pending items in custom post type
  11. WP Sql query multiple where clause
  12. WordPress SQL query – returning ‘true’ ‘false’ or ‘null’
  13. Modify the structure of data returned by $wpdb
  14. Syntax for $wpdb->prepare when searching in two columns
  15. Confused by $wpdb->prepare
  16. How to display user_nicename and usermeta values by custom query in WordPress?
  17. Optimizing WordPress Queries – Removing Group By ID
  18. How can I combine one field using wpdb and group by?
  19. $wpdb->prepare with ON DUPLICATE KEY UPDATE
  20. how to use $wpdb->prepare to update a custom table
  21. WPDB Placeholders and second argument for prepared statements
  22. Increment integer field in database when WHERE needs to be dynamic [closed]
  23. Custom SQL query ORDER BY term_order
  24. Custom $wpdb returns unexpected time based results
  25. How to left join meta in queries [closed]
  26. wpdb->get_row is selecting the variable as a column name
  27. CREATE TABLE with dbDelta does not create table
  28. $wpdb query outputs php code instead of executing it
  29. wpdb query not working
  30. WordPress wpdb->insert returns int(0) => doesn’t insert anything, no errors!
  31. WPDB SQL Ignore `post_status` Parameter
  32. how to list all post that are in the custom taxonomy using $wpdb
  33. WPDB SQL query SELECT from category
  34. How to use WHERE NOT EXISTS query to avoid duplicate entry using $wpdb to save in custom table?
  35. Creating an Angular factory from custom database table
  36. wpdb->update update the entire table instead of one row
  37. How do I update post based on meta_key in another table?
  38. How to set up prepared query using IN statement
  39. Custom database query to validate data
  40. Alter the main search query to search posts by coauthor user name
  41. Creates only one table and not the other
  42. Protect custom form from SQL injection
  43. looking for a way to allow users to backup the plugin db data(save as)
  44. SQL Query to select post title & post ID from a particular category
  45. Get comments after specific date
  46. query using wpdb in wordpress gets me no result
  47. Get count of rows based if column exists in two different tables
  48. query_vars doesn’t return query string (trying to get data from $wpdb)
  49. How do you properly prepare a %LIKE% SQL statement?
  50. How to parse row results from $wpdb -> get_results
  51. $wpdb->insert() and Values for Datetime Columns?
  52. Change post status based on meta value
  53. Speed optimization of $wpdb->get_results
  54. Wpdb query for comment meta for current post
  55. How to Modify this $wpdb query to accept an array of post statuses
  56. WPDB Update using Conditional Arrays
  57. How to correctly pass values to wpdb->prepare()?
  58. $wpdb->get_results returns empty but value exists
  59. Problem displaying inserted form
  60. Get published posts and pages?
  61. How to get a value-only flat array from $wpdb->get_results when selecting a single column, without foreach()?
  62. How to Instantiate wpdb Object in New File
  63. How do you build a wpdb query dynamically?
  64. Would this WPDB setup result in potential race conditions?
  65. How to update a row in a table in WordPress
  66. Codex: Database Description: meaning of Cardinality
  67. $wpdb->insert() does not Insert record in a table
  68. WordPress SQL Issue not returning correct reselts
  69. WordPress db prepare
  70. $wpdb->prepare with LIKE returning blank array instead of rows
  71. $wpdb update query in plugin only updating one column
  72. Custom Query – Based on user input
  73. how to get db values without using an loop with wpdb->get_results()
  74. How to run wp_insert_post() & wpdb on the background?
  75. Trying to get variable from WP table and toggle its value
  76. Set MySQL variables in WPDB
  77. get_results query with accent
  78. How to prepare an array of values with $wpdb
  79. Is querying wpdb directly and skipping actions provided by WP’s core “wp_update_post” a good idea?
  80. Using “->” in a page to exceute $wpdb query gives error
  81. Why my query does not run with prepare
  82. Exclude specific terms from all queries using posts_where or something similar
  83. wpdb getting avatars and author url
  84. query a newly created table using $wpdb
  85. Get all sticky posts from one user through user ID
  86. WordPress SQL JOIN query
  87. Counting Posts with multiple taxonomy and terms condition using MySQL and AJAX
  88. Any way to use FETCH_KEY_PAIR with $wpdb?
  89. Delete oldest wordpress post (SQL query)
  90. “This message was added in version X” showing a later version than current one
  91. Prepare WPDB with meta key and meta value
  92. How to get row value from wpdb
  93. I am using wpdb but it not working perfectly.but if I dont use form data its work
  94. WPDP related functions look to work but they don’t
  95. why nl2br() is adding an extra ?
  96. get unserialized array without using get_option()
  97. Create Table Failed Column Date DateType
  98. Checking if meta_value exists for any user
  99. Can you create a new wpdb that connects to an SQL (not MYSQL) database? [duplicate]
  100. Get all the related data from WordPress DB
techhipbettruvabetnorabahisbahis forumueduseduseduedueduedueduedueduedu