How to add Foreign Key in database table using Magento 2?

We can add a foreign key for a column of a table using Magento by the below code snippet.

Add foreign key in a column,
Example, you are creating a module and you want to add Store Filter in your module for that case you need to create a relation with store table.
Using below code snippet you can create a relationship with store table.

Here your custom table name is {my_table_name} and this table must contain field name store_id to a relation with store table.
In InstallSchema or UpgradeSchema File, you need to add below code snippet,

<?php
->addForeignKey(
    $installer->getFkName('my_table_name', 'store_id', 'store', 'store_id'),
    'store_id',
    $installer->getTable('store'), /* main table name */
    'store_id',
    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)

“my_table_name” is your table name
“store_id” is your table field name to relate with parent table
“store” Main table to create relation with
“store_id” Main table field name
“ACTION_CASCADE”  used for the type of actions which executes on a matched row of the child table when a parent table row gets update or delete action.

/**
     * Actions used for foreign keys
     */
     ACTION_CASCADE = 'CASCADE';

     ACTION_SET_NULL = 'SET NULL';

     ACTION_NO_ACTION = 'NO ACTION';

     ACTION_RESTRICT = 'RESTRICT';

     ACTION_SET_DEFAULT = 'SET DEFAULT';

Using Above code You can add foreign key in your table which contain relation to store table of Magento.

You can refer blog for add Index to column by How to add Index to table column using Magento 2?

How to rename/change column name for database table using Magento 2?

In Magento 2 Sometimes we have added a column/field in the table for our custom requirement and in future if we need to rename or change that column name, we can rename it using UpgradeSchema.php file.

I have added one field name guestname in sales_order table and I want to rename that field to fullname. You can rename field using changeColumn() in UpgradeSchema.php file.
Using below code snippet you can rename already existed column field from a table.

Upgrade the setup_version number of module from module.xml file. An older version number is 1.0.1 so I have added new version 1.0.2 in module.xml file

Path: Magento22/app/code/Rbj/Training/etc/module.xml

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
	<module name="Rbj_Training" setup_version="1.0.2"/>
</config>

Create UpgradeSchema.php file under Setup folder in your module.Path: Magento22/app/code/Rbj/Training/Setup/UpgradeSchema.php 

<?php
namespace Rbj\Training\Setup;

use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\UpgradeSchemaInterface;

/**
 * Upgrade the Sales_Order Table to remove extra field
 */
class UpgradeSchema implements UpgradeSchemaInterface
{

    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();
        if (version_compare($context->getVersion(), '1.0.2', '<')) {
            $setup->getConnection()->changeColumn(
                $setup->getTable('sales_order'),
                'guestname',
                'fullname',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'length' => 255,
                    'comment' => 'Full name of customer'
                ]
            );
        }
        $setup->endSetup();
    }
}

Using the above code snippet you can change column name from guestname to fullname with 255 character type text

Run command from root directory of your project,php bin/magento setup:upgrade

If you want to drop field name from a table, Remove an extra field from a table

If you want to add the field in table  Add New Field in Database Table Magento 2

How to remove extra column from database table using Magento 2?

In Magento 2 Sometimes we have added an extra column in the table for extended functionality in module and in future, we have removed that functionality so we need to remove extra column field from a database table.

I have added one field name gst in sales_order table and I want to remove extra field from table in future. You can remove field using dropColumn() in UpgradeSchema.php file.
Using below code snippet you can remove already existed column field from table.

Upgrade the setup_version number of module from module.xml file. An older version number is 1.0.1 so I have added new version 1.0.2 in module.xml file

Path: Magento22/app/code/Rbj/Training/etc/module.xml

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
	<module name="Rbj_Training" setup_version="1.0.2"/>
</config>

Create UpgradeSchema.php file under Setup folder in your module.
Path: Magento22/app/code/Rbj/Training/Setup/UpgradeSchema.php

<?php
namespace Rbj\Training\Setup;

use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\UpgradeSchemaInterface;

/**
 * Upgrade the Sales_Order Table to remove extra field
 */
class UpgradeSchema implements UpgradeSchemaInterface
{

    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();
        if (version_compare($context->getVersion(), '1.0.2', '<')) {
            $setup->getConnection()->dropColumn($setup->getTable('sales_order'), 'gst');
        }
        $setup->endSetup();
    }
}

Run command from root directory of your project,
php bin/magento setup:upgrade

If you want to add the field in table  Add New Field in Database Table Magento 2
If you want to rename/change column field name, Refer link Rename Column name in Table Magento 2