Lookup table values

[amiles-eit]

Idea Summary

Add a Quick SQL shorthand syntax to explicitly create lookup tables with a predefined set of values. This would allow both the table structure and its fixed data to be defined in the same Quick SQL script, without needing separate SQL insert statements.

Use Case

When developing a new application schema in APEX, it’s common to define lookup tables for enumerations like statuses, categories, or priority levels.

Quick SQL currently supports /insert and /values to generate random sample data, but there’s no way to specify fixed, curated values inline.

This forces developers to split the workflow: define the lookup table in Quick SQL, then write separate manual INSERT statements.

In collaborative schema design sessions with subject matter experts, it would be faster and clearer if the agreed-upon lookup values could be visible directly in the Quick SQL script.

Possible Solution

Introduce a directive such as /fixed or /enum in Quick SQL that:

1. Optionally marks the table as a lookup table.

2. Generates INSERT statements for the exact values provided.

Example Syntax:

statuses /lookup
  status_name vc50
  /fixed Planned
  /fixed In Progress
  /fixed Complete
  /fixed On Hold

Generated SQL:

CREATE TABLE statuses (
  status_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  status_name VARCHAR2(50) NOT NULL UNIQUE
);
INSERT INTO statuses (status_name) VALUES ('Planned');
INSERT INTO statuses (status_name) VALUES ('In Progress');
INSERT INTO statuses (status_name) VALUES ('Complete');
INSERT INTO statuses (status_name) VALUES ('On Hold');

Benefits:

• Keeps schema definition and seed data in one place.
• Improves collaboration with SMEs during schema workshops.
• Speeds up prototyping and testing by providing realistic enumerations immediately.

[vadim-tropashko]

Wouldn't /values directive fit the purpose?

[alimanfoo]

Hi @vadim-tropashko - I believe the /values directive translates into a constraint on the column values. What I'm looking for is the allowed values to be stored in a separate lookup table.