Invisible columns in SQL

Invisible columns are a neat trick in SQL. They let you add new data to your database without messing up existing queries. This is great when updating an old system. The new columns stay hidden unless you ask for them.

They also boost security. Sensitive data stays out of sight in standard queries. If someone gets unauthorized access, they won’t see the hidden data unless they know how to find it. You control who sees what. This keeps your important information safe.

Benefits of Invisible Columns

• Layered Security: Invisible columns add an extra layer of security. If someone gains unauthorized access, they won’t see the most sensitive data unless they know to query it specifically.
• Controlled Access: These columns don’t appear in SELECT * queries. Only specific queries can access them, allowing you to control who can see this data.
• Simplify Auditing: Use invisible columns to store audit trails, like created_by or last_accessed_by. This keeps audit data secure and out of everyday queries, lowering the risk of tampering.
• Update Old Systems: If you’re working on a legacy system, you might need to add new data fields. But you don’t want to break everything else. Invisible columns let you add these fields without changing how the old system works.
• Limit Data Exposure: Invisible columns keep sensitive information, like personal identifiers or financial data, out of standard queries. This reduces the risk of accidentally exposing this data.

Note: Before using invisible columns, think about whether they’re the right fit for your needs.

Databases That Support Invisible Columns

• MySQL: Supported in MySQL 8.0.23 and later.
• Oracle Database: Supported in Oracle 12c Release 1 (12.1) and later.
• MariaDB: Supported in MariaDB 10.3 and later.
• PostgreSQL: Not supported (use views or conditional logic as alternatives).
• SQL Server: Not supported (use views or computed columns as alternatives).

Employees table with an invisible column

Let’s create an employees table where the salary is an invisible column.

1
CREATE TABLE employees (
2
  id INT,
3
  salary DECIMAL(10, 2) INVISIBLE,
4
  name VARCHAR(100)
5
);

Note: At least one column in a table must be visible. If you try to make all columns invisible, it will result in an error.

Inserting data into the employees table:

1
INSERT INTO employees (id, salary, name) VALUES (1, 50000.00, 'Alice');
2
INSERT INTO employees (id, salary, name) VALUES (2, 60000.00, 'Bob');
3
INSERT INTO employees (id, salary, name) VALUES (3, 55000.00, 'Charlie');

Now, if you run the query SELECT * FROM employees;, the output would look like this.

1
SELECT * FROM employees;

1
+----+--------+
2
| id | name   |
3
+----+--------+
4
|  1 | Alice  |
5
|  2 | Bob    |
6
|  3 | Charlie|
7
+----+--------+

Notice that the salary column, which is marked as invisible, does not appear in the output.

This is the power of invisible columns. They let you hide specific data from your query results, keeping things clean and focused.

The data is still there, but it won’t show up unless you specifically ask for it. This helps reduce clutter in your results and keeps sensitive information out of sight. It’s a simple way to control what data gets exposed in your queries.

The Importance of Default Values

Invisible columns can be declared as NOT NULL. However, if they are, you must provide a default value.

1
CREATE TABLE orders (
2
  order_id INT,
3
  status VARCHAR(50) INVISIBLE NOT NULL DEFAULT 'Pending'
4
);

status is invisible, cannot be null, and has a default value of 'Pending'. If you try to insert data without specifying the status, the default value is used.

1
INSERT INTO orders (order_id) VALUES (1);
2

3
SELECT * FROM orders;

1
+----------+
2
| order_id |
3
+----------+
4
|        1 |
5
+----------+

This query will return just order_id, but the status column still exists and is set to 'Pending'.

• Only the order_id column is visible because the status column is invisible.
• The status column has a default value of 'Pending', but it does not appear in the output of the SELECT * query.

Retrieving Invisible Columns

Invisible columns don’t disappear completely—they are simply hidden. To retrieve them, you must explicitly ask for them in your query.

1
CREATE TABLE orders (
2
  order_id INT,
3
  status VARCHAR(50) INVISIBLE NOT NULL DEFAULT 'Pending'
4
);
5

6
INSERT INTO orders (order_id) VALUES (1);
7
INSERT INTO orders (order_id) VALUES (2);
8
INSERT INTO orders (order_id, status) VALUES (3, 'Shipped');

Now, if you explicitly query for the invisible status column like this:

1
SELECT order_id, status FROM orders;

1
+----------+---------+
2
| order_id | status  |
3
+----------+---------+
4
|        1 | Pending |
5
|        2 | Pending |
6
|        3 | Shipped |
7
+----------+---------+

• Both order_id and status will appear in the results.
• The status for order_id 1 and 2 is shown as 'Pending' because it uses the default value.
• The status for order_id 3 is 'Shipped', as it was explicitly set during the insert.

Surrogate Primary Keys

Imagine you have a table without a primary key, and you want to add one without disrupting the existing structure.

1
CREATE TABLE customers (
2
  customer_id INT,
3
  name VARCHAR(100),
4
  email VARCHAR(100)
5
);

You can add an invisible surrogate primary key to your table, which is a unique identifier for each row.

1
ALTER TABLE customers ADD pkid SERIAL PRIMARY KEY INVISIBLE FIRST;
2

3
INSERT INTO customers (customer_id, name, email) VALUES (1, 'Alice', '[email protected]');
4
INSERT INTO customers (customer_id, name, email) VALUES (2, 'Bob', '[email protected]');
5
INSERT INTO customers (customer_id, name, email) VALUES (3, 'Charlie', '[email protected]');

This primary key won’t clutter your standard query results but will still uniquely identify each row behind the scenes. This is particularly useful when you want to maintain a clean and simple output for your queries but still need a reliable way to reference each record individually.

Now, every row will have a unique primary key (pkid).

1
SELECT * FROM customers;

1
+-------------+---------+--------------------+
2
| customer_id | name    | email              |
3
+-------------+---------+--------------------+
4
|           1 | Alice   | [email protected]  |
5
|           2 | Bob     | [email protected]    |
6
|           3 | Charlie | [email protected]|
7
+-------------+---------+--------------------+

• The pkid column, which is marked as invisible, does not appear in the results.
• Only the visible columns (customer_id, name, and email) are shown.

Remember, to retrieve the invisible pkid column, you must explicitly include it in your SELECT query. Even though it’s hidden by default, you can still access it by specifying it in the list of columns you want to retrieve.

1
SELECT pkid, customer_id, name, email FROM customers;

1
+------+-------------+---------+--------------------+
2
| pkid | customer_id | name    | email              |
3
+------+-------------+---------+--------------------+
4
|    1 |           1 | Alice   | [email protected]  |
5
|    2 |           2 | Bob     | [email protected]    |
6
|    3 |           3 | Charlie | [email protected]|
7
+------+-------------+---------+--------------------+

Surrogate Foreign Keys

Let’s say you have two tables: orders and customers. The orders table references the customers table through a customer_id foreign key.

customers table with an invisible surrogate primary key.

1
CREATE TABLE customers (
2
  customer_id INT,
3
  name VARCHAR(100),
4
  email VARCHAR(100),
5
  pkid INT AUTO_INCREMENT PRIMARY KEY INVISIBLE
6
);

• AUTO_INCREMENT automatically generates a unique value for the pkid column.
• PRIMARY KEY ensures that pkid is the primary key.
• INVISIBLE makes the pkid column invisible by default in queries like SELECT *.

Create the orders table with a surrogate foreign key that references the invisible primary key (pkid) from the customers table.

1
CREATE TABLE orders (
2
  order_id INT,
3
  order_date DATE,
4
  customer_pkid INT INVISIBLE,
5
  FOREIGN KEY (customer_pkid) REFERENCES customers(pkid)
6
);

Inserting dummy data into the customers table.

1
INSERT INTO customers (customer_id, name, email) VALUES (1, 'Alice', '[email protected]');
2
INSERT INTO customers (customer_id, name, email) VALUES (2, 'Bob', '[email protected]');

• customer_pkid is a foreign key referencing the pkid in the customers table and is also invisible.

Inserting dummy data into the orders table.

1
INSERT INTO orders (order_id, order_date, customer_pkid) VALUES (101, '2024-08-26', 1);
2
INSERT INTO orders (order_id, order_date, customer_pkid) VALUES (102, '2024-08-27', 2);

When you run a SELECT * FROM orders; query, the output will only show the visible columns:

1
SELECT * FROM orders;

1
+----------+------------+
2
| order_id | order_date |
3
+----------+------------+
4
|      101 | 2024-08-26 |
5
|      102 | 2024-08-27 |
6
+----------+------------+

The customer_pkid column, which acts as the foreign key, is invisible by default and doesn’t appear in this output. To retrieve the foreign key along with the order details, you can explicitly include it in your query.

1
SELECT order_id, order_date, customer_pkid FROM orders;

1
+----------+------------+--------------+
2
| order_id | order_date | customer_pkid|
3
+----------+------------+--------------+
4
|      101 | 2024-08-26 |            1 |
5
|      102 | 2024-08-27 |            2 |
6
+----------+------------+--------------+

Joining Tables Using Surrogate Keys

If you want to join the orders and customers tables to get more information, you can do so using the surrogate keys.

1
SELECT o.order_id, o.order_date, c.name, c.email
2
FROM orders o
3
JOIN customers c
4
ON o.customer_pkid = c.pkid;

1
+----------+------------+-------+------------------+
2
| order_id | order_date | name  | email            |
3
+----------+------------+-------+------------------+
4
|      101 | 2024-08-26 | Alice | [email protected] |
5
|      102 | 2024-08-27 | Bob   | [email protected]   |
6
+----------+------------+-------+------------------+

Invisible columns are a handy tool for managing data in SQL. They’re perfect for adding features to an old system without breaking anything or for keeping your queries clean and simple. Understanding how to create and use these columns gives you more control over your database.

Next time you’re working on a database, think about whether some columns should be invisible. It’s a small feature, but it can make a big difference.