Tables, Columns and Constraints

Every attribute a column can take, which keys are constraints, and where an index is declared in db_schema.xml.

Syntax<column xsi:type="varchar" name="sku" nullable="false" length="64" comment="SKU"/>

A table is a <table> node with four useful attributes: name, resource (which database connection to use, one of default, checkout or sales), engine (innodb or memory) and comment. Inside it go columns, then constraints, then indexes.

Columns

Every column carries xsi:type and name. The type decides which further attributes are allowed. int and smallint take unsigned, padding and identity. varchar, char and varbinary take length. decimal, float and real take precision and scale. timestamp takes on_update. nullable and comment apply to all of them, and so does default except on text, mediumtext, longtext and the blob types, whose declarations accept no default attribute at all. The available types include int, smallint, decimal, varchar, text, mediumtext, blob, boolean, date, datetime, timestamp, varbinary and json.

Keys and indexes

A key is a <constraint> with an xsi:type of primary, unique or foreign, plus a referenceId that names it. primary and unique list their columns as child <column> nodes. foreign is written entirely in attributes: table, column, referenceTable, referenceColumn and onDelete, where onDelete is CASCADE, SET NULL or NO ACTION.

An index that is not a key is an <index> node with referenceId and an indexType of btree, fulltext or hash.

Two forms of the same tag

<column ... /> and <column ...></column> are identical XML. Core uses the self-closing form for anything with no children. Either way, bin/magento setup:upgrade applies it.

Example

Example · xml
<!-- app/code/Soundscode/Enquiry/etc/db_schema.xml -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="soundscode_enquiry" resource="default" engine="innodb" comment="Storefront enquiry">
        <column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="true"
                comment="Entity ID"/>
        <column xsi:type="varchar" name="reference" nullable="false" length="32" comment="Public reference"/>
        <column xsi:type="varchar" name="email" nullable="false" length="255" comment="Customer email"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="unique" referenceId="SOUNDSCODE_ENQUIRY_REFERENCE">
            <column name="reference"/>
        </constraint>
        <index referenceId="SOUNDSCODE_ENQUIRY_EMAIL" indexType="btree">
            <column name="email"/>
        </index>
    </table>

    <table name="soundscode_enquiry_reply" resource="default" engine="innodb" comment="Reply to an enquiry">
        <column xsi:type="int" name="reply_id" unsigned="true" nullable="false" identity="true"
                comment="Reply ID"/>
        <column xsi:type="int" name="enquiry_id" unsigned="true" nullable="false" comment="Parent enquiry"/>
        <column xsi:type="mediumtext" name="body" nullable="true" comment="Reply body"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="reply_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="SOUNDSCODE_ENQUIRY_REPLY_ENQUIRY_ID"
                    table="soundscode_enquiry_reply" column="enquiry_id"
                    referenceTable="soundscode_enquiry" referenceColumn="entity_id"
                    onDelete="CASCADE"/>
    </table>
</schema>

When to use it

  • Create a parent and child table pair with a cascading foreign key.
  • Store a money value without rounding errors.
  • Enforce uniqueness across two columns in the database rather than in PHP.
  • Put a quote-related table on the checkout connection for a split database.

More examples

The type-specific attributes, side by side

One table showing each family of type-specific attributes in place. Note resource="checkout": on a store with split databases, quote-related tables live on the checkout connection, and declaring the wrong resource puts the table on the wrong server. Use this as the reference when you cannot remember whether length belongs on a decimal (it does not) or scale on a varchar (it does not).

Example · xml
<!-- app/code/Soundscode/Enquiry/etc/db_schema.xml (excerpt) -->
<table name="soundscode_quote_fee" resource="checkout" engine="innodb" comment="Per-quote handling fee">
    <!-- int: unsigned, padding, identity -->
    <column xsi:type="int" name="fee_id" unsigned="true" padding="10" nullable="false" identity="true"
            comment="Fee ID"/>
    <!-- decimal: precision and scale, never float, for money -->
    <column xsi:type="decimal" name="fee_amount" scale="4" precision="20" nullable="false" default="0"
            comment="Fee amount"/>
    <!-- smallint standing in for a flag, as core does -->
    <column xsi:type="smallint" name="is_applied" unsigned="false" nullable="false" default="0"
            comment="Is fee applied"/>
    <!-- timestamp: on_update="true" means ON UPDATE CURRENT_TIMESTAMP -->
    <column xsi:type="timestamp" name="updated_at" on_update="true" nullable="false"
            default="CURRENT_TIMESTAMP" comment="Updated at"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="fee_id"/>
    </constraint>
</table>

A composite unique key and a fulltext index

Child column order inside a composite key is the column order in the resulting index, so put the column you filter on first. The unique constraint here is what stops the same tag being attached twice, enforced by the database rather than by application code. The fulltext index is a MySQL index for your own LIKE-free searching; storefront catalog search does not use it.

Example · xml
<!-- app/code/Soundscode/Enquiry/etc/db_schema.xml (excerpt) -->
<table name="soundscode_enquiry_tag" resource="default" engine="innodb" comment="Tag on an enquiry">
    <column xsi:type="int" name="enquiry_id" unsigned="true" nullable="false" comment="Enquiry ID"/>
    <column xsi:type="varchar" name="tag" nullable="false" length="64" comment="Tag"/>
    <column xsi:type="mediumtext" name="notes" nullable="true" comment="Internal notes"/>
    <constraint xsi:type="unique" referenceId="SOUNDSCODE_ENQUIRY_TAG_ENQUIRY_ID_TAG">
        <column name="enquiry_id"/>
        <column name="tag"/>
    </constraint>
    <index referenceId="SOUNDSCODE_ENQUIRY_TAG_NOTES" indexType="fulltext">
        <column name="notes"/>
    </index>
</table>

A nullable foreign key with SET NULL

onDelete and nullable are one decision, not two. SET NULL requires the local column to accept null, and MySQL refuses the foreign key if it does not. Pick SET NULL when the child row still means something after the parent goes (an enquiry outlives the admin who handled it), and CASCADE when it does not (a reply means nothing without its enquiry).

Example · xml
<!-- app/code/Soundscode/Enquiry/etc/db_schema.xml (excerpt) -->
<table name="soundscode_enquiry" resource="default" engine="innodb" comment="Storefront enquiry">
    <column xsi:type="int" name="entity_id" unsigned="true" nullable="false" identity="true"
            comment="Entity ID"/>
    <!-- nullable, because onDelete="SET NULL" has to be able to write null here -->
    <column xsi:type="int" name="assigned_admin_id" unsigned="true" nullable="true"
            comment="Admin user handling the enquiry"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="entity_id"/>
    </constraint>
    <constraint xsi:type="foreign" referenceId="SOUNDSCODE_ENQUIRY_ASSIGNED_ADMIN_ID_ADMIN_USER_USER_ID"
                table="soundscode_enquiry" column="assigned_admin_id"
                referenceTable="admin_user" referenceColumn="user_id"
                onDelete="SET NULL"/>
</table>

Related

m2 schema declarativem2 schema whitelistm2 schema changesm2 schema practicem2 perf mysql

Discussion

  • Be the first to comment on this lesson.