How to Create Database Tables in Magento 2

July 6, 2021 The Online Helper

To create a database table in Magento 2, you need to create a file in the module folder:

Directory

app/code/VendorName/ModuleName/Setup/InstallSchema.php

Content for this file

<?php

namespace VendorName\ModuleName\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;

class InstallSchema implements InstallSchemaInterface
{
     public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
     {
         $installer = $setup;
         $installer->startSetup();

        //new table script will be there

        $installer->endSetup();
     }
}

/**
 * Create table 'TOH_table1'
 */
 $table = $installer->getConnection()->newTable(
     $installer->getTable('magefan_faq')
 )->addColumn(
     'customer_id',
     \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
     null,
     ['identity' => true, 'nullable' => false, 'primary' => true],
     'Customer ID'
 )->addColumn(
     'name',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     255,
     ['nullable' => true],
     'Customer Name'
 )->addColumn(
     'customer_email',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     '64k',
     ['nullable' => true],
     'Customer Email'
 )->addColumn(
     'customer_address',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     '64k',
     ['nullable' => true],
     'Customer Address'
 )->addColumn(
     'phone_no',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     100,
     ['nullable' => true, 'default' => null],
     'Customer Phone no'
 )->addColumn(
     'post_code',
     \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
     255,
     ['nullable' => true],
     'PostCode'
 )->setComment(
     'TOH Table1'
);
$installer->getConnection()->createTable($table);

The following methods.

Method addColumn:

/**
 * Adds a column to the table.
 *
 * $options contains additional options for the column. Allowed parameters:
 * - 'unsigned', is used only for numeric types. By default, gets the FALSE value;
 * - 'precision', is used only for numeric types. By default, gets a value from the $ size parameter, otherwise 0;
 * - 'scale', is used only for numeric types. By default, gets a value from the $ size parameter, otherwise equal to 10;
 * - 'default', no default value;
 * - 'nullable', default value: TRUE;
 * - 'primary', adds a column to the primary index. By default, the column is not added to the primary index;
 * - 'primary_position', the column from the primary index is used. The default value: number of primary columns + 1;
 * - 'identity' or 'auto_increment'. no default value;
 *
 * @param string $name - column name;
 * @param string $type - column data type;
 * @param string|integer|array $size - length (size) of the column;
 * @param array $options array with additional options;
 * @param string $comment - comment to the column;
 * @return $this
 */
 public function addColumn($name, $type, $size = null, $options = [], $comment = null)

Method setComment:

/**
 * Sets a comment to the table.
 *
 * @param string $comment - text of the comment;
 * @return $this
 */
 public function setComment($comment)

Method addIndex:

/**
 * Adds an index to the table.
 *
 * @param string $indexName - index name;
 * @param array or string $fields  - an array of column names or a column name;
 * @param array $options - an array of additional options;
 * @return $this
 */
 public function addIndex($indexName, $fields, $options = [])

Method addForeignKey:

/**
 * Adds foreign key to the table.
 *
 * @param string $fkName - the name of the foreign key;
 * @param string $column  - the name of the column key for the foreign key;
 * @param string $refTable - the name of the foreign table;
 * @param string $refColumn - name of foreign columns;
 * @param string $onDelete - event to delete a line;
 * @return $this
 */
 public function addForeignKey($fkName, $column, $refTable, $refColumn, $onDelete = null)

All these methods are decleared in vendor/magento/framework/DB/Ddl/Table.php.

Start the module installation process, run the following command:

php bin/magento setup:upgrade

If you are looking for Magento Developers, visit Magento Web Agency.

How to Create Database Tables in Magento 2

Recent post