Repository: sqlfluff/sqlfluff

Issue:
[Postgres Dialect] `PRIMARY KEY` keyword treated as identifiers
<!--
-- If this is a parsing or linting issue, please include a minimal SQL example which reproduces the issue, along with the `sqlfluff parse` output, `sqlfluff lint` output and `sqlfluff fix` output when relevant. Finally, please add any labels that are related to the issue before submitting so that it can be addressed easily! --
-->

## Expected Behaviour

```sql
CREATE TABLE main.sample(
    id char(4) NOT NULL,
    label text NOT NULL,
    title text NOT NULL,
    PRIMARY KEY(id)
);
```

Linting the DDL statement would be success.

## Observed Behaviour

Unfortunately linting is failed. I would guess that `PRIMARY KEY` was not treated as keyword. 🤔 

```shell
sqlfluff lint --dialect postgres sample.sql
== [sample.sql] FAIL
L:   5 | P:   5 | L014 | Inconsistent capitalisation of unquoted identifiers.
L:   5 | P:   5 | L029 | Keywords should not be used as identifiers.
```

If we change the DDL statement as below, linting will success.

```sql
CREATE TABLE main.sample(
    id char(4) NOT NULL PRIMARY KEY,
    label text NOT NULL PRIMARY KEY,
    title text NOT NULL
);
```

## Steps to Reproduce

Run lint with the DDL statement I've posted first on this.

## Version
Include the output of `sqlfluff --version` along with your Python version

```shell
$ sqlfluff --version
sqlfluff, version 0.3.6

$ python -V
Python 3.9.1
```

## Configuration

No configurations, using default.

Discussion:
codecov[bot]: # [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719?src=pr&el=h1) Report
> Merging [#719](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719?src=pr&el=desc) (ccf9921) into [master](https://codecov.io/gh/sqlfluff/sqlfluff/commit/bc8e439c1b1ea60c6e522113ce600ba83609a8e0?el=desc) (bc8e439) will **not change** coverage.
> The diff coverage is `n/a`.

[![Impacted file tree graph](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719/graphs/tree.svg?width=650&height=150&src=pr&token=maM8kLgCmn)](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719?src=pr&el=tree)

```diff
@@           Coverage Diff           @@
##           master     #719   +/-   ##
=======================================
  Coverage   95.52%   95.52%           
=======================================
  Files          92       92           
  Lines        5993     5993           
=======================================
  Hits         5725     5725           
  Misses        268      268           
```

| Flag | Coverage Δ | |
|---|---|---|
| dbt017-py38 | `56.33% <ø> (ø)` | |
| dbt018-py38 | `56.36% <ø> (ø)` | |
| py36 | `94.65% <ø> (ø)` | |
| py37 | `94.65% <ø> (ø)` | |
| py38 | `94.61% <ø> (ø)` | |
| py39 | `94.61% <ø> (ø)` | |

Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags#carryforward-flags-in-the-pull-request-comment) to find out more.

| [Impacted Files](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [src/sqlfluff/core/dialects/dialect\_ansi.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719/diff?src=pr&el=tree#diff-c3JjL3NxbGZsdWZmL2NvcmUvZGlhbGVjdHMvZGlhbGVjdF9hbnNpLnB5) | `99.66% <ø> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719?src=pr&el=footer). Last update [bc8e439...ccf9921](https://codecov.io/gh/sqlfluff/sqlfluff/pull/719?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

sti0: > Hey @sti0 thanks for submitting this, this is a good catch 🙂. Could you take a read of https://github.com/sqlfluff/sqlfluff/blob/master/test/fixtures/parser/README.md and add in the required .sql file for the .yml file you've correctly added to be parsed and compared against?

@NiallRees the .sql file was already there. I added the parse specification for it (yml file)
NiallRees: > > Hey @sti0 thanks for submitting this, this is a good catch 🙂. Could you take a read of https://github.com/sqlfluff/sqlfluff/blob/master/test/fixtures/parser/README.md and add in the required .sql file for the .yml file you've correctly added to be parsed and compared against?
> 
> @NiallRees the .sql file was already there. I added the parse specification for it (yml file)
> 
> https://github.com/sqlfluff/sqlfluff/blob/master/test/fixtures/parser/ansi/create_table_a_pk_unique_fk_constraints.sql

Ah apologies!

Patch:
diff --git a/src/sqlfluff/core/dialects/dialect_ansi.py b/src/sqlfluff/core/dialects/dialect_ansi.py
index 58960025c..4b211eb15 100644
--- a/src/sqlfluff/core/dialects/dialect_ansi.py
+++ b/src/sqlfluff/core/dialects/dialect_ansi.py
@@ -1526,8 +1526,8 @@ class CreateTableStatementSegment(BaseSegment):
                 Bracketed(
                     Delimited(
                         OneOf(
-                            Ref("ColumnDefinitionSegment"),
                             Ref("TableConstraintSegment"),
+                            Ref("ColumnDefinitionSegment"),
                         ),
                         delimiter=Ref("CommaSegment"),
                     )


Test patch:
diff --git a/test/fixtures/parser/ansi/create_table_a_pk_unique_fk_constraints.yml b/test/fixtures/parser/ansi/create_table_a_pk_unique_fk_constraints.yml
new file mode 100644
index 000000000..c87df6044
--- /dev/null
+++ b/test/fixtures/parser/ansi/create_table_a_pk_unique_fk_constraints.yml
@@ -0,0 +1,62 @@
+file:
+    statement:
+        create_table_statement:
+            - keyword: "create"
+            - keyword: "table"
+            - table_reference:
+                  identifier: "table1"
+            - start_bracket: "("
+            - column_definition:
+                  identifier: "c1"
+                  data_type:
+                      data_type_identifier: "INT"
+            - comma: ","
+            - column_definition:
+                  identifier: "c2"
+                  data_type:
+                      data_type_identifier: "INT"
+            - comma: ","
+            - column_definition:
+                  identifier: "c3"
+                  data_type:
+                      data_type_identifier: "INT"
+            - comma: ","
+            - table_constraint_definition:
+                  - keyword: "PRIMARY"
+                  - keyword: "KEY"
+                  - start_bracket: "("
+                  - column_reference:
+                        identifier: "c1"
+                  - end_bracket: ")"
+            - comma: ","
+            - table_constraint_definition:
+                  - keyword: "UNIQUE"
+                  - start_bracket: "("
+                  - column_reference:
+                        identifier: "c2"
+                  - comma: ","
+                  - column_reference:
+                        identifier: "c3"
+                  - end_bracket: ")"
+            - comma: ","
+            - table_constraint_definition:
+                  - keyword: "FOREIGN"
+                  - keyword: "KEY"
+                  - start_bracket: "("
+                  - column_reference:
+                        identifier: "c2"
+                  - comma: ","
+                  - column_reference:
+                        identifier: "c3"
+                  - end_bracket: ")"
+                  - keyword: "REFERENCES"
+                  - column_reference:
+                        identifier: "table2"
+                  - start_bracket: "("
+                  - column_reference:
+                        identifier: "c2_"
+                  - comma: ","
+                  - column_reference:
+                        identifier: "c3_"
+                  - end_bracket: ")"
+            - end_bracket: ")"