Skip to content

How I can reference product SKU as foreign key reference?

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?