Identifiers

Definition

An identifier in SQL is a name given to a database object — a table, column, view, index, constraint, or alias. Identifiers are how you refer to "which table" or "which column" in a statement.

How It Works

Most engines allow identifiers made of letters, digits, and underscores, and require them to not start with a digit (student_id is valid, 1student is not). Two identifier categories matter in practice:

• Unquoted identifiers — the normal case (students, department_id). These are typically case-insensitive in comparison (behavior actually varies: MySQL on Linux is case-sensitive for table names at the filesystem level, while column names generally are not; PostgreSQL lowercases unquoted identifiers automatically).
• Quoted (delimited) identifiers — wrapped in double quotes ("Order", standard SQL / PostgreSQL) or backticks (` Order , MySQL) or brackets ([Order]`, SQL Server). Quoting lets you use a reserved word or spaces/special characters as a name, and usually makes the identifier case-sensitive.

Example: order is a reserved word in most SQL dialects (part of ORDER BY) — to actually name a table "order," you must quote it: ` CREATE TABLE order (...) ` in MySQL.

Edge Cases and Pitfalls

• Naming a column the same as a SQL keyword (select, order, group, date) without quoting it is a very common source of confusing syntax errors.
• Case sensitivity of identifiers is one of the most inconsistent behaviors across dialects and even across operating systems for the same dialect (MySQL table-name case-sensitivity depends on the host OS's filesystem) — never assume Students and students are the same table.
• Identifiers with spaces ("Student Name") require quoting on every reference, which quickly becomes error-prone; the convention of using underscores instead (student_name) exists specifically to avoid this.

Key Takeaways

• Identifiers name database objects; unquoted identifiers follow simple letter/digit/underscore rules and can't start with a digit.
• Quoting (double quotes, backticks, or brackets depending on dialect) allows reserved words or special characters as names, usually at the cost of making the name case-sensitive.
• Good naming convention (lowercase, underscores, avoiding reserved words) avoids the whole quoting problem in the first place.