Ret/håndtér fejl i metadata
Jeg har et script der finder alle fremmednøgler til en given tabel, dvs. constraint navn, tabel og nøgle. Til dette bruger jeg information_schema views'ene i min SQL2014 express:declare @tablename varchar(20)
set @tablename = 'tabelnavn'
select kcu1.constraint_name as constraint_name
,kcu1.table_name as table_name
,kcu1.column_name as foreign_key
,kcu1.ordinal_position as fk_ordinal_position
,kcu2.constraint_name as referenced_constraint_name
,kcu2.table_name as foreign_table_name
,kcu2.column_name as foreign_column_name
,kcu2.ordinal_position as referenced_ordinal_position
from information_schema.referential_constraints as rc
inner join information_schema.key_column_usage as kcu1 on (kcu1.constraint_catalog = rc.constraint_catalog
and kcu1.constraint_schema = rc.constraint_schema
and kcu1.constraint_name = rc.constraint_name)
left join information_schema.key_column_usage as kcu2 on (kcu2.constraint_catalog = rc.unique_constraint_catalog
and kcu2.constraint_schema = rc.unique_constraint_schema
and kcu2.constraint_name = rc.unique_constraint_name
and kcu2.ordinal_position = kcu1.ordinal_position)
where (rc.constraint_schema = 'dbo')
and (kcu1.table_name = @tablename)
order by kcu1.column_name
set @tablename = 'tabelnavn'
select kcu1.constraint_name as constraint_name
,kcu1.table_name as table_name
,kcu1.column_name as foreign_key
,kcu1.ordinal_position as fk_ordinal_position
,kcu2.constraint_name as referenced_constraint_name
,kcu2.table_name as foreign_table_name
,kcu2.column_name as foreign_column_name
,kcu2.ordinal_position as referenced_ordinal_position
from information_schema.referential_constraints as rc
inner join information_schema.key_column_usage as kcu1 on (kcu1.constraint_catalog = rc.constraint_catalog
and kcu1.constraint_schema = rc.constraint_schema
and kcu1.constraint_name = rc.constraint_name)
left join information_schema.key_column_usage as kcu2 on (kcu2.constraint_catalog = rc.unique_constraint_catalog
and kcu2.constraint_schema = rc.unique_constraint_schema
and kcu2.constraint_name = rc.unique_constraint_name
and kcu2.ordinal_position = kcu1.ordinal_position)
where (rc.constraint_schema = 'dbo')
and (kcu1.table_name = @tablename)
order by kcu1.column_name
For langt de fleste får jeg data korrekt retur, men så er der lige undtagelserne, hvor oplysningerne er NULL:
Der er regstreret en fremmednøgle i kcu1, men felterne referenced_constraint_name, foreign_table_name, foreign_column_name og referenced_ordinal_poisition er NULL. Jeg har prøvet at droppe de forkerte constraints og oprette dem igen (alter table drop og add), men de felter forbliver blanke.
Tabellen er naturligvis tjekket og fundet helt korrekt oprettet. På basis af den har jeg prøvet at lave en helt simpel tabel, baseret på tabellens oprindelige script:
CREATE TABLE [dbo].[test]
(
[ref] [int] IDENTITY(1,1) NOT NULL,
[bruger_ref] [int] NULL,
CONSTRAINT [fk_test_bruger] FOREIGN KEY([bruger_ref]) REFERENCES [dbo].[bruger] ([ref]),
CONSTRAINT [pk_test] PRIMARY KEY ([ref])
)
(
[ref] [int] IDENTITY(1,1) NOT NULL,
[bruger_ref] [int] NULL,
CONSTRAINT [fk_test_bruger] FOREIGN KEY([bruger_ref]) REFERENCES [dbo].[bruger] ([ref]),
CONSTRAINT [pk_test] PRIMARY KEY ([ref])
)
Det dur heller ikke. Andre tabeller, oprettet på lignende måde, har ingen problemer med at pege over i bruger-tabellen; data vises korrekt.
Hvad foregår der? Er der en måde at rette op på det? Er der en anden, mere sikker måde at finde fremmednøgler på? Databasen er meget stringent opbygget og alle constraints er på plads.
Løsning fundet. Der er på et eller andet tidspunkt blevet oprettet et indeks sideløbende med primærnøglens for bruger-tabellen. De efterfølgende oprettelser, gik ikke på primærnøglens men på indekset. Fjernede alle constraints der pegede forkert, slettede indekset og oprettede alle constraints igen.
(jeg snupper points selv :-) )
