Implement unenforced constraints on CreateTable DDL statement

[canss3ns]

According to your (Google) own words, defining unenforced primary keys can make a difference. Having the SQLAlchemy DDL constructs implementation now emit the necessary statements would be really neat.

By this I mean for a python statement like:

from sqlalchemy import Column, Date, String, Table
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import CreateTable

t1 = Table('some_dataset.some_table',
           MetaData(),
           Column('name', String, primary_key),
           )
engine = create_engine('bigquery://')
print(engine.dialect.ddl_compiler(engine.dialect, CreateTable(t1)))

Have the following statement emitted:

CREATE TABLE `some_dataset.some_table` (
        `name` STRING NOT NULL
        PRIMARY KEY (name) NOT ENFORCED
)

Instead of the following actual one:

CREATE TABLE `some_dataset.some_table` (
        `name` STRING NOT NULL
)

Would make things much nicer.

Similarly, for explicit constraint (example from you blog post), if:

from sqlalchemy import Column, Date, ForeignKey, PrimaryKeyConstraint, String, Table
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import CreateTable

Inventory = Table('inventory',
                  MetaData(), 
                  Column('inv_date_sk',
                         ForeingKey('date_dim.d_date_sk')),
                  Column('inv_item_sk',
                         ForeignKey('item.i_item_sk')),
                  Column('inv_warehouse_sk',
                         ForeignKey('warehouse.w_warehouse_sk'),
                  PrimaryKeyConstraint('inv_data_sk', 'inv_item_sk', 'inv_warehouse_sk'),
                  )
engine = create_engine('bigquery://')
print(engine.dialect.ddl_compiler(engine.dialect, CreateTable(Inventory)))

Would produce the following SQL statement:

CREATE TABLE inventory (
   inv_date_sk INT64 REFERENCES date_dim(d_date_sk) NOT ENFORCED,
   inv_item_sk INT64 REFERENCES item(i_item_sk) NOT ENFORCED,
   inv_warehouse_sk INT64 REFERENCES warehouse(w_warehouse_sk) NOT ENFORCED,
   inv_quantity_on_hand INT64,
   PRIMARY KEY(inv_date_sk, inv_item_sk, inv_warehouse_sk) NOT ENFORCED
);

I'd reckon that, to not change the existing behavior unexpectedly for the users, it could be necessary to add a feature toggle. So that one can electivelly enable the feature or not.

E.g. a dialect keyword maybe?

t1 = Table('inventory',
           MetaData(), 
           Column('name', String, primary_key=True),
           bigquery_emitconstraints=True,  # Defaults to False
           )

Only if bigquery_emitconstraints is set to true, are the constraints part of the SQL statement.