SQL Identifiers
Introduction
SQL identifiers are names assigned to database objects and query elements. Databases, schemas, tables, columns, views, indexes, constraints, sequences, procedures, functions, triggers, roles, and aliases are all referred to through identifiers. Keywords describe what SQL should do; identifiers tell SQL which named objects or results participate.
SELECT employee_name
FROM employees;
In this statement, SELECT and FROM are SQL keywords. employee_name is a column identifier and employees is a table identifier. This distinction is fundamental to reading parser errors, designing schemas, writing joins, and generating dynamic SQL safely.
Identifiers are more than labels. They become part of application queries, reports, migrations, metadata, documentation, monitoring, and support procedures. A stable, descriptive naming system reduces ambiguity and makes SQL understandable without requiring someone to translate every object name.
Keywords, Identifiers, Literals, and Operators
Each token in an SQL statement has a role:
SELECT salary
FROM employees
WHERE department_id = 10;
| Component | Examples | Purpose |
|---|---|---|
| Keywords | SELECT, FROM, WHERE | Define SQL action and structure |
| Identifiers | salary, employees, department_id | Name database objects |
| Literal | 10 | Represents a fixed data value |
| Operator | = | Performs a comparison |
String literals use single quotes in standard SQL. Identifiers use no delimiters when they follow ordinary naming rules, or identifier-specific delimiters when quoting is required. Confusing these categories can turn a column reference into literal text or cause a parser failure.
Common Types of Identifiers
| Object type | Example identifier | Typical reference |
|---|---|---|
| Database | company_db | Connection or catalog context |
| Schema | hr | hr.employees |
| Table | employees | FROM employees |
| Column | employee_name | SELECT employee_name |
| View | active_customers | FROM active_customers |
| Index | idx_customers_email | Schema and plan metadata |
| Constraint | fk_orders_customer | Error and schema metadata |
| Routine | calculate_tax | Function or procedure call |
| Alias | e | Temporary query scope |
Object categories have different uniqueness scopes. A table name may need to be unique within a schema, while a column name needs to be unique only within its table. Constraint and index rules vary by product. Check the target DBMS rather than assuming one global namespace.
Database, Schema, and Table Identifiers
A database identifier selects a database-level container, while a schema identifier selects a namespace inside a database or catalog. The exact hierarchy differs across products. PostgreSQL connections normally target one database and then use schemas. SQL Server commonly exposes server, database, schema, and object levels. MySQL often uses database and schema as closely related concepts.
CREATE SCHEMA hr;
CREATE TABLE hr.employees (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL
);
hr is the schema identifier and employees is the table identifier. A table name should represent the entity or event stored in its rows, such as customers, orders, or payment_attempts.
Column Identifiers
A column identifier names one attribute or derived field in a table, view, query result, or table-valued expression. Good names describe meaning rather than display position or storage implementation.
SELECT
customer_id,
customer_name,
email_address,
created_at
FROM customers;
Avoid generic columns such as id, name, value, or date when they become ambiguous across joins and reports. Domain-specific identifiers such as customer_id, order_status, and payment_received_at are easier to interpret.
View, Index, and Constraint Identifiers
Views, indexes, and constraints should receive stable descriptive names because those names appear in metadata, execution plans, migration scripts, monitoring, and errors.
CREATE VIEW active_customers AS
SELECT customer_id, customer_name
FROM customers
WHERE customer_status = 'ACTIVE';
CREATE INDEX idx_customers_email
ON customers (email_address);
ALTER TABLE customers
ADD CONSTRAINT uq_customers_email
UNIQUE (email_address);
A system-generated constraint name may be hard to recognize during an incident. Explicit names such as pk_customers, fk_orders_customer, uq_customers_email, and ck_orders_total_nonnegative communicate type, owner, and purpose.
Aliases Are Temporary Identifiers
An alias is a temporary name created within a statement or query block. A table alias shortens qualification, while a column alias labels an output expression.
SELECT
e.employee_name AS name,
e.salary * 12 AS annual_salary
FROM employees AS e;
e, name, and annual_salary are aliases. Their scope is limited to the relevant statement or query block. In many databases, once a table alias is introduced, later references in that query block must use the alias rather than the original table name.
Select-list aliases are created logically after WHERE, so they are normally unavailable to that clause. They are commonly available to ORDER BY, which operates later. Product-specific alias extensions exist, but portable SQL should follow the standard processing model.
Qualified Identifiers
Qualification combines identifiers with dots to provide context. The most common forms are table.column and schema.table. Some products also support database, catalog, or server components.
SELECT
o.order_id,
c.customer_name
FROM sales.orders AS o
JOIN crm.customers AS c
ON c.customer_id = o.customer_id;
sales.orders and crm.customers are schema-qualified table identifiers. o.order_id is a qualified column identifier. Qualification resolves ambiguity and documents the intended source, especially when joined tables contain columns with the same name.
Ambiguous and Duplicate Identifiers
A query becomes ambiguous when an unqualified column name exists in more than one visible source.
-- Ambiguous if both tables contain customer_id
SELECT customer_id
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id;
-- Clear
SELECT c.customer_id
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id;
Duplicate aliases create a similar problem. Every visible source in one query block should have a unique alias. Select-list output names can sometimes repeat, but duplicate labels create fragile mappings for applications, exports, and reporting tools.
Regular and Delimited Identifiers
A regular identifier follows the DBMS rules for unquoted names. A delimited or quoted identifier is enclosed in identifier delimiters and can contain otherwise invalid characters, spaces, reserved words, or preserved letter case.
-- Regular identifiers
SELECT order_id
FROM customer_orders;
-- Delimited identifiers
SELECT "Order Number"
FROM "Customer Orders";
The SQL Server identifier documentation describes regular and delimited classes. Delimited names are valid tools, but they should solve a real interoperability or legacy requirement rather than make ordinary schemas harder to use.
Identifier Quote Characters
| Database or standard | Common identifier delimiter | Example |
|---|---|---|
| Standard SQL | Double quotes | "Customer Orders" |
| PostgreSQL | Double quotes | "MixedCaseName" |
| Oracle | Double quotes | "MixedCaseName" |
| MySQL | Backticks by default | `Customer Orders` |
| SQL Server | Brackets or configured double quotes | [Customer Orders] |
String values use single quotes; identifier delimiters serve a different purpose. Do not write SELECT 'employee_name' FROM employees when the goal is to read the column. That expression returns the literal text employee_name for every row.
Why Minimal Quoting Is Usually Simpler
Quoted identifiers can preserve case and allow spaces, punctuation, or reserved words, but every future reference may need exact matching delimiters and case. ORMs, migration tools, ad hoc queries, and cross-database scripts must reproduce the naming decision.
Avoid names such as "Order Details", "select", or "Customer-ID" when order_details, selection_rule, or customer_id communicates the same concept. Quoting remains appropriate for legacy databases and externally defined schemas, but it should not be a default excuse for weak naming.
Spaces, Hyphens, Dots, and Special Characters
Spaces and hyphens usually require quoting. A hyphen can otherwise look like subtraction, while a dot separates qualification components. Special characters can have product-specific meaning, such as SQL Server's # prefix for temporary tables and @ for variables.
Use underscores to separate words in portable regular identifiers. customer_order_item remains readable, requires no quoting, and behaves predictably in scripts and tools. Do not embed qualification punctuation in a single object name.
Starting Characters, Numbers, and Length Limits
Regular identifiers generally begin with a letter or an allowed product-specific character such as underscore. Digits are commonly permitted after the first character, as in address_line_1. Starting a name with a number usually requires quoting.
Identifier length limits vary by database and object type. PostgreSQL commonly limits identifiers to 63 bytes in default builds, while SQL Server commonly allows up to 128 characters for many identifiers. MySQL documents separate limits for different schema objects. Do not design names at the exact maximum; migration tools, generated constraint suffixes, and multibyte characters can expose edge cases.
Identifier Case Sensitivity
Keyword case and identifier case are separate topics. Unquoted identifiers are often case-folded or compared according to database rules, while quoted identifiers preserve spelling and may become case-sensitive. Collation, operating system, object type, and server configuration can also matter.
A reliable cross-database strategy is to create and reference ordinary object identifiers consistently in lowercase snake case without quotes. This reduces surprises when scripts move between developer systems, CI, containers, and production.
PostgreSQL Case Behavior
PostgreSQL folds unquoted identifiers to lowercase. The identifiers employees, EMPLOYEES, and Employees therefore resolve to the same lowercase name when unquoted. Quoted identifiers preserve case.
CREATE TABLE "EmployeeRecords" (
"EmployeeID" INTEGER
);
-- Exact quoting is required later
SELECT "EmployeeID"
FROM "EmployeeRecords";
The PostgreSQL lexical-structure documentation notes that its lowercase folding differs from the SQL standard's uppercase folding. Mixing quoted mixed-case names with unquoted names is a common source of relation-not-found errors.
Oracle, SQL Server, and MySQL Case Behavior
Oracle normally folds unquoted identifiers to uppercase and preserves quoted spelling. SQL Server identifier comparison depends largely on catalog or database collation; case-sensitive configurations can distinguish names that a case-insensitive configuration treats as equal.
MySQL table and database name behavior can depend on the operating system and the lower_case_table_names configuration because names may map to file-system objects. The MySQL identifier case documentation recommends a consistent lowercase convention to avoid platform-transfer problems.
These differences make names that differ only by case a poor design. Do not create orders and Orders as separate objects even if one environment permits it.
Reserved Words as Identifiers
Reserved words belong to SQL grammar and are unsafe as unquoted object names. A word may be reserved in one product or version but not another. Names such as order, group, select, user, table, and key can create conflicts.
Quoting can make a reserved identifier legal, but renaming is often better. Prefer customer_orders over "order", user_account over "user", and group_name over "group". Review reserved-word lists for the current and target database version during upgrades.
Choosing a Naming Convention
A naming convention defines predictable choices for case, word separation, singular or plural table names, keys, foreign keys, constraints, indexes, timestamps, Boolean columns, and approved abbreviations. Consistency matters more than whether a team chooses singular or plural table names.
Lowercase snake case is broadly practical:
customers
customer_id
customer_name
order_items
unit_price
created_at
Camel case and Pascal case can work in products and tools that handle them consistently, but quoted mixed-case identifiers can impose exact-case references. A convention should optimize readability, portability, and tool compatibility rather than imitate application-language style automatically.
Table and Column Naming
Table identifiers should represent entities, relationships, events, or facts. customers, order_items, and payment_attempts reveal content. Avoid vague tables such as data, records, master, or details without domain context.
Column names should represent attributes with stable business meaning. Avoid physical-position names such as field1 and value2. Avoid encoding display labels, data lengths, or current types into names. A column named customer_name_varchar100 becomes misleading as requirements evolve.
Primary and Foreign Key Naming
A predictable key convention makes relationships easy to recognize. A common pattern names the customer key customer_id in both the customers table and referencing tables such as orders.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);
This consistency makes joins intuitive. If several foreign keys reference the same parent for different roles, express the role, such as created_by_user_id and approved_by_user_id.
Boolean, Date, Time, and Status Names
Boolean columns should read as clear conditions: is_active, has_discount, or can_retry. Avoid inverted or ambiguous names that make true difficult to interpret.
Timestamp identifiers should describe the event: created_at, updated_at, approved_at, and deleted_at. Use _date when only a calendar date is stored. A generic date or timestamp provides no business meaning.
Status columns should identify the subject, such as order_status or payment_status. This avoids ambiguity when several status values appear in a joined result.
Constraint and Index Naming
Constraint and index identifiers should encode enough context for operators and developers to recognize them without becoming excessively long.
| Object | Pattern | Example |
|---|---|---|
| Primary key | pk_table | pk_orders |
| Foreign key | fk_child_parent | fk_orders_customer |
| Unique constraint | uq_table_columns | uq_customers_email |
| Check constraint | ck_table_rule | ck_orders_total_nonnegative |
| Index | idx_table_columns | idx_orders_created_at |
Names should reflect actual indexed order and constraint ownership. If a migration changes an index definition substantially, review whether its identifier remains truthful.
Avoid Cryptic and Type-Based Prefixes
Abbreviations such as cust, txn, or qty may be acceptable when formally defined and universally understood. Uncontrolled abbreviations create multiple names for the same concept and make search difficult. Prefer full words where they remain concise.
Avoid old Hungarian-style or storage prefixes such as tbl_customer, str_name, or int_age. The database catalog already records object type and column data type. Names should describe business meaning, which tends to outlive physical implementation.
Self-Describing SQL
Predictable identifiers make queries read like domain statements:
SELECT
o.order_id,
c.customer_name,
o.order_total,
o.created_at
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id
WHERE o.order_status = 'OPEN';
The relationship and selected facts are understandable without decoding generic labels. Good identifiers reduce comments needed to explain basic structure, allowing comments to focus on non-obvious business rules.
Identifier Scope and Uniqueness
Identifier uniqueness is contextual. Columns must be unique within a table, aliases within the relevant query namespace, and many schema objects within a schema. Temporary objects, local variables, parameters, common table expressions, and routine-local names have their own scopes.
Do not assume two products enforce the same namespace. For example, index and constraint name scopes differ. Migration tools should validate names on the actual target DBMS rather than only in an abstract schema model.
Identifiers and Database Metadata
System catalogs and INFORMATION_SCHEMA expose identifiers for tables, columns, constraints, views, routines, and privileges. Tools use this metadata to generate migrations, map ORM models, build documentation, inspect dependencies, and perform schema comparisons.
Stable names improve observability. A database error naming fk_orders_customer communicates more than a generated hash. A slow-query plan naming idx_orders_created_at reveals the access path. Identifier quality therefore affects production support as well as development readability.
Renaming Identifiers Safely
Renaming a table or column can break application SQL, stored procedures, views, triggers, ETL jobs, BI reports, permissions, ORM mappings, tests, documentation, and monitoring. Some database dependencies are tracked automatically, while external consumers are not.
Treat a rename as a compatibility change. Search source repositories and metadata, inventory consumers, create a versioned migration, update dependent code, test deployment and rollback, and coordinate release order. For distributed systems, an expand-contract transition may temporarily support both old and new representations.
Identifier Stability
Choose names that remain meaningful as implementation changes. Avoid environment names such as dev_customers or prod_orders inside shared schema definitions; environment belongs in deployment context. Avoid version suffixes unless they represent a deliberate public contract rather than a temporary migration.
Do not encode current length or type into a name. A column named phone_varchar20 becomes incorrect when international requirements expand it. Stable identifiers express concepts such as phone_number.
Temporary and System-Generated Identifiers
Temporary table syntax differs by product. SQL Server commonly uses #local_temp and ##global_temp, PostgreSQL supports CREATE TEMP TABLE, and other products use their own rules. Temporary object scope can be session, transaction, or connection dependent.
Databases and frameworks may generate identifiers for constraints, indexes, sequences, temporary objects, or ORM aliases. Generated names are convenient but can vary across environments or exceed product limits. Explicit stable names are often better for long-lived schema objects.
Identifier, Object ID, Primary Key, Alias, and Literal
These concepts are often confused:
- An identifier is a name used by SQL, such as
customers. - An internal object ID is a DBMS metadata value that identifies an object internally.
- A primary key is data that uniquely identifies a row.
- An alias is a temporary identifier within a statement.
- A literal is a fixed data value such as
'ACTIVE'.
A column named customer_id is an identifier. A stored value such as 1042 in that column is data. The primary-key constraint defines how row identity is enforced; the column's name alone does not.
Unicode Identifiers
Modern databases may permit Unicode letters in identifiers, but support varies across database engines, drivers, tools, shells, file systems, and team keyboards. Unicode can be appropriate for externally governed schemas, yet it can reduce portability and make visually similar characters difficult to distinguish.
For broadly shared application schemas, conservative ASCII identifiers are usually easier to type, search, review, and automate. This decision does not restrict Unicode data stored in columns.
Identifier Portability
Portable identifiers avoid reserved words, spaces, punctuation, mixed-case quoted names, product-specific prefix characters, and maximum-length boundaries. Use a common character set, consistent lowercase spelling, and stable qualification patterns.
Portability also requires testing. A name valid in PostgreSQL may conflict with a SQL Server keyword or exceed an Oracle or MySQL object-specific length limit. Validate schema migrations against every supported database and version.
Dynamic Identifier Security
Database parameters represent values, not SQL grammar. A parameter can safely supply a customer ID, but it normally cannot replace a table name, column identifier, or sort direction.
-- Value position: bind as a parameter
WHERE customer_id = :customer_id
-- Identifier position: do not bind raw user input
ORDER BY approved_application_column
If an application must select a dynamic identifier, map the user's choice to a strict application-owned allowlist. Then quote the approved identifier with a trusted DBMS or driver API where required. Concatenating arbitrary input into identifier positions creates injection and authorization risk even when data values are parameterized.
Common Identifier Errors
- Using a reserved word as an unquoted table or column name.
- Writing a column identifier in single quotes and returning literal text.
- Mixing quoted mixed-case names with unquoted references.
- Misspelling a column, table, schema, or alias.
- Using the original table name after an alias replaces it in the query block.
- Omitting qualification when several sources contain the same column name.
- Connecting to the wrong database or relying on the wrong default schema.
- Using spaces, hyphens, or punctuation without the correct delimiter.
- Assuming identifier case behaves identically across platforms.
- Concatenating an untrusted dynamic object name into SQL.
Troubleshooting an Invalid Identifier
When the database reports an unknown, invalid, or ambiguous identifier, check the name systematically:
- Verify spelling in the database catalog rather than only in application code.
- Confirm that the intended table or view contains the column.
- Check whether a table alias must be used.
- Qualify ambiguous columns with the correct source alias.
- Verify schema qualification and the session search path or default schema.
- Check quoted case and identifier delimiters.
- Confirm the connection points to the expected database and version.
- Review whether a migration creating or renaming the object was applied.
An error near an identifier can originate earlier in the statement. Missing commas, unmatched quotes, or incorrect keyword order can cause the parser to interpret a valid name in the wrong context.
A Recommended Naming Style
A practical baseline for many application databases is:
- Use lowercase snake case for ordinary identifiers.
- Use descriptive names without spaces or special punctuation.
- Avoid reserved words and unnecessary quoting.
- Choose one singular or plural table convention and apply it consistently.
- Name key columns predictably, such as
customer_id. - Name foreign-key columns after their relationship role.
- Use event-focused timestamps such as
approved_at. - Give constraints and indexes stable descriptive names.
- Maintain an approved abbreviation glossary.
- Validate names against all supported DBMS versions.
Complete Naming Example
CREATE TABLE customers (
customer_id INTEGER,
customer_name VARCHAR(150) NOT NULL,
email_address VARCHAR(255) NOT NULL,
customer_status VARCHAR(20) NOT NULL,
created_at TIMESTAMP NOT NULL,
CONSTRAINT pk_customers
PRIMARY KEY (customer_id),
CONSTRAINT uq_customers_email
UNIQUE (email_address),
CONSTRAINT ck_customers_status
CHECK (customer_status IN ('ACTIVE', 'INACTIVE'))
);
CREATE INDEX idx_customers_status_created_at
ON customers (customer_status, created_at);
The identifiers express entity, attributes, keys, rules, and access paths. Keywords such as CREATE, TABLE, PRIMARY KEY, and CHECK provide structure, while literals such as 'ACTIVE' represent data values.
Practical Naming Workflow
Before creating an object, identify the business concept and its scope. Decide whether the object stores an entity, relationship, event, summary, or operational artifact. Choose the simplest descriptive name that follows the project convention, then check reserved words, case rules, length, uniqueness, and supported database products.
For a relationship, name both the foreign-key column and constraint consistently. For a customer-order relationship, orders.customer_id referencing customers.customer_id makes the join intuitive. Review the migration as SQL and confirm that generated names from tools match the convention.
Identifier Documentation
Document naming conventions, approved abbreviations, schema boundaries, table purposes, column meaning, key relationships, and lifecycle fields. Consistent identifiers reduce documentation needs but do not eliminate them; a clear name still cannot fully express units, allowed values, privacy classification, or business ownership.
Automated schema documentation can extract identifier names and comments from catalogs, but human review should verify that names and descriptions still match the implemented behavior.
Keyword and Identifier Quick Comparison
| SQL component | Example | Meaning |
|---|---|---|
| Keyword | SELECT | Defines an SQL operation |
| Table identifier | employees | Names a table |
| Column identifier | salary | Names a column |
| Qualified identifier | e.salary | Names a column through a source alias |
| Alias | e | Provides a temporary query name |
| Literal | 'ACTIVE' | Represents a fixed data value |
| Operator | > | Performs a comparison |
Interview Questions and Answers
What is an SQL identifier? It is a name assigned to a database object or query element, such as a table, column, schema, constraint, index, routine, or alias.
What is the difference between a regular and delimited identifier? A regular identifier follows ordinary unquoted naming rules. A delimited identifier is quoted and can preserve case or contain otherwise invalid characters and reserved words.
Why should identifiers be qualified? Qualification removes ambiguity, identifies the intended schema or source, and makes joins easier to understand.
Are identifiers case-sensitive? The answer depends on the DBMS, object type, quoting, collation, configuration, and sometimes operating system. A consistent lowercase unquoted strategy avoids many problems.
Can table and column names be parameters? Normally no. Parameter binding represents data values, not SQL grammar. Dynamic identifiers require strict allowlist mapping and trusted quoting.
Why explicitly name constraints? Stable names improve migrations, metadata inspection, error messages, monitoring, and support.
What makes an identifier portable? Simple characters, consistent lowercase spelling, moderate length, no reserved words, no spaces or punctuation, and no dependence on vendor-specific quoting.
Key Takeaway
SQL identifiers are the names used to reference database objects and query elements. Keywords define actions and structure; identifiers select the database, schema, table, column, view, index, constraint, routine, or alias involved.
SQL IDENTIFIER = DATABASE OBJECT NAME
customers - table
customer_id - column
crm.customers - qualified table
c.customer_name - qualified column
idx_customers_email - index
fk_orders_customer - constraint
c - alias
A strong general strategy uses lowercase snake case, descriptive names, no spaces, no reserved words, minimal quoting, predictable key names, and consistent qualification. The best identifier makes SQL understandable without requiring someone to explain what every object means.