What Are ‘Invisible Columns’ in MariaDB and How to Use Them?

mariadbmariadb-10.3terminology

I saw a new feature Invisible Columns in MariaDB 10.3.x. What are practical use cases for DBA and web developer? When to use this feature?

Columns can be given an INVISIBLE attribute in a CREATE TABLE or ALTER TABLE statement. These columns will then not be listed in the results
of a SELECT * statement, nor do they need to be assigned a value in an
INSERT statement, unless INSERT explicitly mentions them by name.

Since SELECT * does not return the invisible columns, new tables or
views created in this manner will have no trace of the invisible
columns. If specifically referenced in the SELECT statement, the
columns will be brought into the view/new table, but the INVISIBLE
attribute will not.

Invisible columns can be declared as NOT NULL, but then require a
DEFAULT value

Best Answer

This is of course not standard SQL, and generally goofy. Instead follow best practices,

Never use INVISIBLE columns.
Never use SELECT * in production code.
Always secure columns that you don't want other people to get access to with encryption, or SQL-Standard column-level permissions.

MariaDB's `INVISIBLE` Columns

It seems that the intention is to permit MariaDB users to continue to use SELECT * which is almost always against a DBA's best practices. So now you can use SELECT *, and then add to the table without mutating the result returned by SELECT *.

CREATE TABLE users ( id INT, username varchar(255) );

And you have an export that dumps users with SELECT *. Later if you wish to add a column, you can mark it as INVISIBLE and maintain the result

ALERT TABLE users
  ADD COLUMN password varchar(255) INVISIBLE;

Post- ADD COLUMN you're still hilariously safe without learning that no one in their right mind would use SELECT * for in that case.

Though only one type of invisibility is exposed to the user, MariaDB internally has four different types of invisibility. For more information see, MDEV-10177 Invisible columns. Invisibility also effects INSERT, find more information on the ticket

Other Databases

As an aside, Oracle calls this feature INVISIBLE, and DB2 calls it IMPLICITLY HIDDEN. In other dialects of SQL where this isn't supported you would just create a VIEW,

CREATE user_view AS SELECT id, username FROM users;

Related Solutions

What do you call something that’s not the primary key

User terms surrogate key and natural key for primary key as a variant.

Surrogate Key:

Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Natural Key:

Keys are natural if the attribute it represents is used for identification independently of the database schema. What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.

Surrogate Keys vs Natural Keys for Primary Key

MariaDB – Update Column Values Using Select from External Database in Trigger

Your UPDATE statement is wrong. I've edited your TRIGGER and the modifications I did and errors I saw:

Errors:

You tried to drop a different TRIGGER that the one you're going to create. DROP TRIGGER IF EXISTS veevan.account_ai$$.
Why do you need the variable @TargetLastMod?. You didn't use it in the whole trigger.

Modifications:

I edited the DROP TRIGGER with veevan.address_vod__c_ai instead veevan.account_ai.
I used a JOIN with the same fields of your SELECT, with (address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c) in the UPDATE.

TRIGGER:

USE `veevan`;
DELIMITER $$
DROP TRIGGER IF EXISTS veevan.address_vod__c_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veevan`.`address_vod__c_ai` AFTER INSERT ON `address_vod__c` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c LIMIT 0,1); 

        UPDATE pdone.targets
        JOIN veevan.address_vod__c AS avc ON (address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c)
        SET
        `address1` = avc.address1,
        `address2` = avc.address2,
        `city` = avc.city,
        `state` = avc.state,
        `zip` = avc.zip,
        `phone` = avc.phone,
        `fax` = avc.fax,
        `lastSyncAt` = NOW(),
        `updatedAt` = NOW()
        WHERE address_vod__c.account_vod__c = NEW.account_vod__c
            AND external_id_vod__c IS NOT NULL
            AND address_vod__c.primary_vod__c = 1
            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veevan.recordtype WHERE name = 'Professional_vod');

    END IF;
END$$
DELIMITER ;

Try it!