---
title: "Invisible columns in SQL"
description: "Invisible columns stay hidden from queries until you name them. How they work in MySQL 8.0.23+, Oracle and MariaDB, and alternatives in PostgreSQL."
canonical: "https://www.trevorlasn.com/blog/invisible-columns-in-sql"
author: "Trevor I. Lasn"
published: "2024-08-26"
last-updated: "2024-08-26"
topics: ["Software engineering"]
---
# Invisible columns in SQL
Invisible columns stay hidden from queries until you name them. How they work in MySQL 8.0.23+, Oracle and MariaDB, and alternatives in PostgreSQL.
By Trevor I. Lasn · Published 2024-08-26 · https://www.trevorlasn.com/blog/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.
```sql
CREATE TABLE employees (
id INT,
salary DECIMAL(10, 2) INVISIBLE,
name VARCHAR(100)
);
```
*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:
```sql
INSERT INTO employees (id, salary, name) VALUES (1, 50000.00, 'Alice');
INSERT INTO employees (id, salary, name) VALUES (2, 60000.00, 'Bob');
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.
```sql
SELECT * FROM employees;
```
```text frame="terminal"
+----+--------+
| id | name |
+----+--------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie|
+----+--------+
```
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.
```sql
CREATE TABLE orders (
order_id INT,
status VARCHAR(50) INVISIBLE NOT NULL DEFAULT 'Pending'
);
```
`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.
```sql
INSERT INTO orders (order_id) VALUES (1);
SELECT * FROM orders;
```
```text frame="terminal"
+----------+
| order_id |
+----------+
| 1 |
+----------+
```
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.
```sql
CREATE TABLE orders (
order_id INT,
status VARCHAR(50) INVISIBLE NOT NULL DEFAULT 'Pending'
);
INSERT INTO orders (order_id) VALUES (1);
INSERT INTO orders (order_id) VALUES (2);
INSERT INTO orders (order_id, status) VALUES (3, 'Shipped');
```
Now, if you explicitly query for the invisible status column like this:
```sql
SELECT order_id, status FROM orders;
```
```text frame="terminal"
+----------+---------+
| order_id | status |
+----------+---------+
| 1 | Pending |
| 2 | Pending |
| 3 | Shipped |
+----------+---------+
```
* 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.
```sql
CREATE TABLE customers (
customer_id INT,
name VARCHAR(100),
email VARCHAR(100)
);
```
You can add an invisible surrogate primary key to your table, which is a unique identifier for each row.
```sql
ALTER TABLE customers ADD pkid SERIAL PRIMARY KEY INVISIBLE FIRST;
INSERT INTO customers (customer_id, name, email) VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO customers (customer_id, name, email) VALUES (2, 'Bob', 'bob@example.com');
INSERT INTO customers (customer_id, name, email) VALUES (3, 'Charlie', 'charlie@example.com');
```
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`).
```sql
SELECT * FROM customers;
```
```text frame="terminal"
+-------------+---------+--------------------+
| customer_id | name | email |
+-------------+---------+--------------------+
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
| 3 | Charlie | charlie@example.com|
+-------------+---------+--------------------+
```
* 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.
```sql
SELECT pkid, customer_id, name, email FROM customers;
```
```text frame="terminal"
+------+-------------+---------+--------------------+
| pkid | customer_id | name | email |
+------+-------------+---------+--------------------+
| 1 | 1 | Alice | alice@example.com |
| 2 | 2 | Bob | bob@example.com |
| 3 | 3 | Charlie | charlie@example.com|
+------+-------------+---------+--------------------+
```
### 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.
```sql
CREATE TABLE customers (
customer_id INT,
name VARCHAR(100),
email VARCHAR(100),
pkid INT AUTO_INCREMENT PRIMARY KEY INVISIBLE
);
```
* `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.
```sql
CREATE TABLE orders (
order_id INT,
order_date DATE,
customer_pkid INT INVISIBLE,
FOREIGN KEY (customer_pkid) REFERENCES customers(pkid)
);
```
Inserting dummy data into the `customers` table.
```sql
INSERT INTO customers (customer_id, name, email) VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO customers (customer_id, name, email) VALUES (2, 'Bob', 'bob@example.com');
```
* `customer_pkid` is a foreign key referencing the `pkid` in the `customers` table and is also invisible.
Inserting dummy data into the `orders` table.
```sql
INSERT INTO orders (order_id, order_date, customer_pkid) VALUES (101, '2024-08-26', 1);
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:
```sql
SELECT * FROM orders;
```
```text frame="terminal"
+----------+------------+
| order_id | order_date |
+----------+------------+
| 101 | 2024-08-26 |
| 102 | 2024-08-27 |
+----------+------------+
```
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.
```sql
SELECT order_id, order_date, customer_pkid FROM orders;
```
```text frame="terminal"
+----------+------------+--------------+
| order_id | order_date | customer_pkid|
+----------+------------+--------------+
| 101 | 2024-08-26 | 1 |
| 102 | 2024-08-27 | 2 |
+----------+------------+--------------+
```
### 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.
```sql
SELECT o.order_id, o.order_date, c.name, c.email
FROM orders o
JOIN customers c
ON o.customer_pkid = c.pkid;
```
```text frame="terminal"
+----------+------------+-------+------------------+
| order_id | order_date | name | email |
+----------+------------+-------+------------------+
| 101 | 2024-08-26 | Alice | alice@example.com |
| 102 | 2024-08-27 | Bob | bob@example.com |
+----------+------------+-------+------------------+
```
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.