In my db_schema.xml
I made a table with a pivot one:
<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="blog_posts"
resource="default"
engine="innodb"
comment="Blog articles">
<column xsi:type="int" name="blog_post_id" unsigned="true" nullable="false" identity="true" comment="primary_key"/>
<column xsi:type="varchar" name="title" comment="Blog Post's title" />
<column xsi:type="text" name="post" comment="Blog Post's content" />
<column xsi:type="timestamp" name="creation_dt" comment="Created Datetime" on_update="false" nullable="false" default="CURRENT_TIMESTAMP" />
<column xsi:type="timestamp" name="update_dt" comment="Last update Datetime" on_update="true" nullable="true" default="CURRENT_TIMESTAMP" />
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="blog_post_id"/>
</constraint>
</table>
<table name="blog_posts_to_sku"
resource="default"
engine="innodb"
comment="Products related to a blog article">
<column xsi:type="int" name="blog_post_id" unsigned="true" nullable="false" identity="true" comment=""/>
<column xsi:type="int" name="product_sku" unsigned="true" nullable="false" identity="true" comment=""/>
<constraint xsi:type="foreign"
referenceId="BLOG_POST_ID"
table="blog_posts_to_sku"
column="blog_post_id"
referenceTable="blog_posts"
referenceColumn="blog_post_id"
onDelete="CASCADE"/>
</table>
</schema>
I intend to establish a foreign key reference in this table using the product_sku. However, I’m uncertain about which table and column to use for referencing the product SKU, as it’s stored in a different table.
Need a guidance on the tables and columns where I can set up the foreign key relationship for the blog_posts_to_sku.product_sku field?