[Sqlalchemy-tickets] Issue #4299: There is no way to create a foreign key to a BLANK_SCHEMA column name. (zzzeek/sqlalchemy)

[Michael B. <iss...@bi...>]

New issue 4299: There is no way to create a foreign key to a BLANK_SCHEMA column name.
https://bitbucket.org/zzzeek/sqlalchemy/issues/4299/there-is-no-way-to-create-a-foreign-key-to

Michael Bayer:

from #3978:

There is no way to create a foreign key to a BLANK_SCHEMA column name.  Similarly, the lookup in MetaData.tables is inconsistent vs. ForeignKey string lookup, inconsistent vs. declarative.

These should be made completely consistent.    The rule can be made as follows:

1. the MetaData.tables collection stays as a dictionary with a single, fully qualified key.   I'd rather not make this dictionary something "magic", as particularly it needs to iterate accurately.

2. MetaData gets a new accessor resolve_table(<string>).  Rules are:

    a. if MetaData has no "schema", this is a direct key into the  .tables dictionary.

    b. string has no schema name.   table returned is <metadata_schema_name>.table.   if <metadata_schema_name>.table doesn't exist, raise an error.   The error can include a note if there is a BLANK_SCHEMA.<tablename> present, but doesn't assume that's what the caller wants.

   c. string takes on special form "BLANK_SCHEMA.<tablename>".   then we look up on "blank" schema.   This also works in case "a.".  not sure I like the actual symbol name there, need to think about it.

Declarative needs to use resolve_table().   ForeignKey unfortunately needs to determine its exact schemaname.tablename.colname before the remote table potentially exists.
   


```
#!python

from sqlalchemy import *

m = MetaData(schema="foo")

a1 = Table(
    'a', m,
    Column('id', Integer, primary_key=True)
)

a2 = Table(
    'a', m,
    Column('id', Integer, primary_key=True),
    schema=BLANK_SCHEMA
)

b = Table(
    'b', m,
    Column('id', Integer, primary_key=True),
    Column('aid', ForeignKey('a.id'))
)

assert list(b.c.aid.foreign_keys)[0].column is a1.c.id

Table(
    'c', m,
    Column('id', Integer, primary_key=True),
    Column('aid', ForeignKey('foo.a.id'))
)

Table(
    'd', m,
    Column('id', Integer, primary_key=True),
    Column('aid', ForeignKey(a2.c.id))
)




```