Magento – In which table the attribute set ids are stored in magento 2

Step 1: Create app/etc/modules/MyProject_Catalog.xml

Contents:

<?xml version="1.0"?>
<config>
    <modules>
        <MyProject_Catalog>
            <active>true</active>
            <codePool>local</codePool>
        </MyProject_Catalog>
    </modules>
</config>

Step 2: Create app/code/local/MyProject/Catalog/etc/config.xml

Contents:

<?xml version="1.0"?>
<config>
    <modules>
        <MyProject_Catalog>
            <version>1.0.0</version>
        </MyProject_Catalog>
    </modules>
    <global>
        <blocks>
            <adminhtml>
                <rewrite>
                    <catalog_product_grid>MyProject_Catalog_Block_Adminhtml_Catalog_Product_Grid</catalog_product_grid>
                </rewrite>
            </adminhtml>
        </blocks>
    </global>
</config>

Step 3: Create app/code/local/MyProject/Catalog/Block/Adminhtml/Catalog/Product/Grid.php

Contents:

<?php
class MyProject_Catalog_Block_Adminhtml_Catalog_Product_Grid 
    extends Mage_Adminhtml_Block_Catalog_Product_Grid
{
    public function _prepareColumns()
    {
        parent::_prepareColumns();

        $this->removeColumn('set_name');

        $sets = Mage::getResourceModel('eav/entity_attribute_set_collection')
            ->setEntityTypeFilter(Mage::getModel('catalog/product')->getResource()->getTypeId())
            ->setOrder('attribute_set_name', 'asc')
            ->load()
            ->toOptionHash();

        $this->addColumnAfter('set_name',
            array(
                'header'=> Mage::helper('catalog')->__('Attrib. Set Name'),
                'width' => '100px',
                'index' => 'attribute_set_id',
                'type'  => 'options',
                'options' => $sets,
        ), 'type');

        $this->sortColumnsByOrder();

        return $this;
    }
}

Step 4 Clear 'configuration' cache type if enabled.

Whenever you save attribute from the admin panel, Magento will run validation on it and it will change your backend type based on input type. You can see that in getBackendTypeByInput method inside Mage_Adminhtml_Catalog_Product_AttributeController class.

   /**
     * Detect backend storage type using frontend input type
     *
     * @return string backend_type field value
     * @param string $type frontend_input field value
     */
    public function getBackendTypeByInput($type)
    {
        $field = null;
        switch ($type) {
            case 'text':
            case 'gallery':
            case 'media_image':
            case 'multiselect':
                $field = 'varchar';
                break;

            case 'image':
            case 'textarea':
                $field = 'text';
                break;

            case 'date':
                $field = 'datetime';
                break;

            case 'select':
            case 'boolean':
                $field = 'int';
                break;

            case 'price':
                $field = 'decimal';
                break;
        }

        return $field;
    }

You can use this as a guideline when adding new attributes via install script. If you are adding attribute via admin you don't have to worry about that. Of course, if you modify backend or frontend type directly from DB, Magento will validate and 'fix' the attribute on next save.

In other words you should never, ever change attribute properties directly from DB. Especially if the attribute already has some values saved. Doing this will result in attribute values being written in different entity type tables, which is what happened in your case. This will result in various issues, e.g. attribute disappearing from layered navigation, not searchable or filterable, cannot be saved, etc.

Fix for this is simple. First you need to determine what your backend/input types are. Then you need to make sure that the combination is valid using the code above and that it won't be changed in the future by manual editing.

Let's assume you want decimal/price. This means that your values should be saved in catalog_product_entity_decimal table. You will have to check all the other catalog_product_entity_* tables and remove all entries associated with your attribute_id. If you need to preserve the data you can also export rows before you delete them (without value_id) and import them in catalog_product_entity_decimal table.

This should fix your attribute. Remember to reindex after this is done.