id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30183	SQLite instrospection.get_constraints() for unique constraints return wrong count of constraints and doesn't have columns for all items	Pavel Tyslacki	Pavel Tyslacki	"Examples below has next issues:
1. `None` name of constraint
2. `'columns': []` for some constraints (get by `SELECT sql FROM sqlite_master` parsing)
3. duplicates of constraints from different places (get by `SELECT sql FROM sqlite_master` parsing and from idexes)
4. ignoring of some constraints when it created for same columns

This issue blocking fixing #30172 for sqlite, because migrations can use introspection for constraint detection and changing.

{{{
cursor.execute(""""""
    CREATE TABLE ""test1"" (
        ""id"" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        ""name"" varchar(255) NOT NULL, 
        ""email"" varchar(255) NOT NULL
    )
"""""")
print(connection.introspection.get_constraints(cursor, 'test1'))
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}
}}}
{{{
cursor.execute(""""""
    CREATE TABLE ""test2"" (
        ""id"" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        ""name"" varchar(255) NOT NULL UNIQUE, 
        ""email"" varchar(255) NOT NULL
    )
"""""")
print(connection.introspection.get_constraints(cursor, 'test2'))
# None: {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False}
# 'sqlite_autoindex_test2_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True}
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}
}}}
{{{
cursor.execute(""""""
    CREATE TABLE ""test3"" (
        ""id"" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        ""name"" varchar(255) NOT NULL, 
        ""email"" varchar(255) NOT NULL, 
        CONSTRAINT ""name_uniq"" UNIQUE (""name"")
    )
"""""")
print(connection.introspection.get_constraints(cursor, 'test3'))
# 'name_uniq': {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'sqlite_autoindex_test3_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}
}}}
{{{
cursor.execute(""""""
    CREATE TABLE ""test4"" (
        ""id"" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        ""name"" varchar(255) NOT NULL UNIQUE, 
        ""email"" varchar(255) NOT NULL, 
        CONSTRAINT ""name_uniq"" UNIQUE (""name"")
    )
"""""")
print(connection.introspection.get_constraints(cursor, 'test4'))
# None: {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'name_uniq': {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'sqlite_autoindex_test4_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}
}}}
{{{
cursor.execute(""""""
    CREATE TABLE ""test5"" (
        ""id"" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        ""name"" varchar(255) NOT NULL UNIQUE, 
        ""email"" varchar(255) NOT NULL, 
        CONSTRAINT ""name_uniq"" UNIQUE (""name"")
    )
"""""")
cursor.execute(""""""
    CREATE UNIQUE INDEX ""test5_uniq"" ON ""test5"" (""name"")
"""""")
print(connection.introspection.get_constraints(cursor, 'test5'))
# None: {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'name_uniq': {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'test5_uniq': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# 'sqlite_autoindex_test5_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}
}}}
{{{
cursor.execute(""""""
    CREATE TABLE ""test6"" (
        ""id"" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        ""name"" varchar(255) NOT NULL UNIQUE, 
        ""email"" varchar(255) NOT NULL, 
        CONSTRAINT ""name_uniq"" UNIQUE (""email"")
    )
"""""")
cursor.execute(""""""
    CREATE UNIQUE INDEX ""test6_uniq"" ON ""test6"" (""name"", ""email"")
"""""")
print(connection.introspection.get_constraints(cursor, 'test6'))
# None: {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'name_uniq': {'unique': True, 'columns': [], 'primary_key': False, 'foreign_key': False, 'check': False, 'index': False},
# 'test6_uniq': {'columns': ['name', 'email'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# 'sqlite_autoindex_test6_2': {'columns': ['email'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# 'sqlite_autoindex_test6_1': {'columns': ['name'], 'primary_key': False, 'unique': True, 'foreign_key': False, 'check': False, 'index': True},
# '__primary__': {'columns': ['id'], 'primary_key': True, 'unique': False, 'foreign_key': False, 'check': False, 'index': False}
}}}
"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed		Simon Charette	Accepted	1	0	0	0	0	0