References Rules

This section contains 6 rule(s) related to references.

Rule Summary

Code	Name	Aliases	Fix Compatible
RF01	references.from	L026	❌
RF02	references.qualification	L027	❌
RF03	references.consistent	L028	✅
RF04	references.keywords	L029	❌
RF05	references.special_chars	L057	❌
RF06	references.quoting	L059	✅

---

RF01: references.from

References cannot reference objects not present in FROM clause.

Property	Value
Name	references.from
Aliases	L026
Groups	all, core, references
Auto-fixable	No ❌

NOTE

This rule is disabled by default for Athena, BigQuery, Databricks, DuckDB, Hive, Redshift, SOQL and SparkSQL due to the support of things like structs and lateral views which trigger false positives. It can be enabled with the force_enable = True flag.

Anti-pattern

In this example, the reference vee has not been declared.

SELECT
    vee.a
FROM foo

Best practice

Remove the reference.

SELECT
    a
FROM foo

Configuration

Option	Description
force_enable	Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].

---

RF02: references.qualification

References should be qualified if select has more than one referenced table/view.

Property	Value
Name	references.qualification
Aliases	L027
Groups	all, references
Auto-fixable	No ❌

NOTE

Except if they're present in a USING clause.

Anti-pattern

In this example, the reference vee has not been declared, and the variables a and b are potentially ambiguous.

SELECT a, b
FROM foo
LEFT JOIN vee ON vee.a = foo.a

Best practice

Add the references.

SELECT foo.a, vee.b
FROM foo
LEFT JOIN vee ON vee.a = foo.a

Configuration

Option	Description
subqueries_ignore_external_references	If True, parent query references are not included as potentially ambiguous in subqueries. Defaults to False. Must be one of [True, False].

---

RF03: references.consistent

Column references should be qualified consistently in single table statements.

Property	Value
Name	references.consistent
Aliases	L028
Groups	all, references
Auto-fixable	Yes ✅

NOTE

For Athena, BigQuery, Hive and Redshift this rule is disabled by default. This is due to historical false positives associated with STRUCT data types. This default behaviour may be changed in the future. The rule can be enabled with the force_enable = True flag.

"consistent" will be fixed to "qualified" if inconsistency is found.

Anti-pattern

In this example, only the reference to b is qualified.

SELECT
    a,
    foo.b
FROM foo

Best practice

Either all column references should be qualified, or all unqualified.

SELECT
    a,
    b
FROM foo

-- Also good

SELECT
    foo.a,
    foo.b
FROM foo

Configuration

Option	Description
force_enable	Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].
single_table_references	The expectation for references in single-table select. Must be one of ['consistent', 'qualified', 'unqualified'].

---

RF04: references.keywords

Keywords should not be used as identifiers.

Property	Value
Name	references.keywords
Aliases	L029
Groups	all, references
Auto-fixable	No ❌

Although unreserved keywords can be used as identifiers, and reserved words can be used as quoted identifiers, best practice is to avoid where possible, to avoid any misunderstandings as to what the alias represents.

NOTE

Note that reserved keywords cannot be used as unquoted identifiers and will cause parsing errors and so are not covered by this rule.

Anti-pattern

In this example, SUM (built-in function) is used as an alias.

SELECT
    sum.a
FROM foo AS sum

Best practice

Avoid keywords as the name of an alias.

SELECT
    vee.a
FROM foo AS vee

Configuration

Option	Description
ignore_words	Comma separated list of words to ignore from rule.
ignore_words_regex	Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.
quoted_identifiers_policy	Types of quoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases', 'table_aliases', 'none'].
unquoted_identifiers_policy	Types of unquoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases', 'table_aliases'].

---

RF05: references.special_chars

Do not use special characters in identifiers.

Property	Value
Name	references.special_chars
Aliases	L057
Groups	all, references
Auto-fixable	No ❌

Anti-pattern

Using special characters within identifiers when creating or aliasing objects.

CREATE TABLE DBO.ColumnNames
(
    [Internal Space] INT,
    [Greater>Than] INT,
    [Less<Than] INT,
    Number# INT
)

Best practice

Identifiers should include only alphanumerics and underscores.

CREATE TABLE DBO.ColumnNames
(
    [Internal_Space] INT,
    [GreaterThan] INT,
    [LessThan] INT,
    NumberVal INT
)

Configuration

Option	Description
additional_allowed_characters	Optional list of extra allowed characters, in addition to alphanumerics (A-Z, a-z, 0-9) and underscores.
allow_space_in_identifier	Should spaces in identifiers be allowed? Must be one of [True, False].
ignore_words	Comma separated list of words to ignore from rule.
ignore_words_regex	Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.
quoted_identifiers_policy	Types of quoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases', 'table_aliases', 'none'].
unquoted_identifiers_policy	Types of unquoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases', 'table_aliases'].

---

RF06: references.quoting

Unnecessary quoted identifier.

Property	Value
Name	references.quoting
Aliases	L059
Groups	all, references
Auto-fixable	Yes ✅

This rule will fail if the quotes used to quote an identifier are (un)necessary depending on the force_quote_identifier configuration. This rule applies to both column references and their aliases. The default (safe) behaviour is designed not to unexpectedly corrupt SQL. That means the circumstances in which quotes can be safely removed depends on the current dialect would resolve the unquoted variant of the identifier (see below for examples).

Additionally this rule may be configured to a more aggressive setting by setting case_sensitive to False, in which case quotes will be removed regardless of the casing of the contained identifier. Any identifiers which contain special characters, spaces or keywords will still be left quoted. This setting is more appropriate for projects or teams where there is more control over the inputs and outputs of queries, and where it's more viable to institute rules such as enforcing that all identifiers are the default casing (and therefore meaning that using quotes to change the case of identifiers is unnecessary).

Dialect group	✅ Example where quotes are safe to remove.	⚠️ Examples where quotes are not safe to remove.
Natively UPPERCASE dialects e.g. Snowflake, BigQuery, TSQL & Oracle.	Identifiers which, without quotes, would resolve to the default casing of FOO i.e. "FOO".	Identifiers where the quotes are necessary to preserve case (e.g. "Foo" or "foo"), or where the identifier contains something invalid without the quotes such as keywords or special characters e.g. "SELECT", "With Space" or "Special&Characters".
Natively lowercase dialects e.g. Athena, Hive & Postgres	Identifiers which, without quotes, would resolve to the default casing of foo i.e. "foo".	Identifiers where the quotes are necessary to preserve case (e.g. "Foo" or "foo"), or where the identifier contains something invalid without the quotes such as keywords or special characters e.g. "SELECT", "With Space" or "Special&Characters".
Case insensitive dialects e.g. duckdb_dialect_ref or sparksql_dialect_ref	Any identifiers which are valid without quotes: e.g. "FOO", "foo", "Foo", "fOo", FOO and foo would all resolve to the same object.	Identifiers which contain something invalid without the quotes such as keywords or special characters e.g. "SELECT", "With Space" or "Special&Characters".

This rule is closely associated with (and constrained by the same above factors) as aliasing.self_alias.column (AL09).

When prefer_quoted_identifiers = False (default behaviour), the quotes are unnecessary, except for reserved keywords and special characters in identifiers.

Anti-pattern

In this example, valid unquoted identifiers, that are not also reserved keywords, are needlessly quoted.

SELECT "foo" as "bar";  -- For lowercase dialects like Postgres
SELECT "FOO" as "BAR";  -- For uppercase dialects like Snowflake

Best practice

Use unquoted identifiers where possible.

SELECT foo as bar;  -- For lowercase dialects like Postgres
SELECT FOO as BAR;  -- For uppercase dialects like Snowflake

-- Note that where the case of the quoted identifier requires
-- the quotes to remain, or where the identifier cannot be
-- unquoted because it would be invalid to do so, the quotes
-- may remain. For example:
SELECT
    "Case_Sensitive_Identifier" as is_allowed,
    "Identifier with spaces or speci@l characters" as this_too,
    "SELECT" as also_reserved_words
FROM "My Table With Spaces"

When prefer_quoted_identifiers = True, the quotes are always necessary, no matter if the identifier is valid, a reserved keyword, or contains special characters.

NOTE

Due to different quotes being used by different dialects supported by SQLFluff, and those quotes meaning different things in different contexts, this mode is generally not sqlfluff fix compatible. SQLite is a narrow exception for prefer_quoted_keywords and supports autofix using prefer_quoted_keyword_style.

Anti-pattern

In this example, a valid unquoted identifier, that is also not a reserved keyword, is required to be quoted.

SELECT 123 as foo

Best practice Use quoted identifiers.

SELECT 123 as "foo" -- For ANSI, ...
-- or
SELECT 123 as `foo` -- For BigQuery, MySql, ...

Configuration

Option	Description
case_sensitive	If False, comparison is done case in-sensitively. Defaults to True. Must be one of [True, False].
ignore_words	Comma separated list of words to ignore from rule.
ignore_words_regex	Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.
prefer_quoted_identifiers	If True, requires every identifier to be quoted. Defaults to False. Must be one of [True, False].
prefer_quoted_keyword_style	Preferred quoting style to use when this rule inserts quoted keyword identifiers. This is only fix-compatible for SQLite. Defaults to double_quotes. Must be one of ['double_quotes', 'backticks'].
prefer_quoted_keywords	If True, requires every keyword used as an identifier to be quoted. Defaults to False. Must be one of [True, False].

---