Cannot use unique index as reference for constraint columns (only unique constraints)

[Arnagos]

It's not possible to use the columns of a unique index to create a new foreign key constraint.
A unique index is just as viable as a unique constraint for the creation of a foreign key constraint between non-primary-key columns.

Is there a way to manually define the target columns? As far as I can see it's always handled through the ReferesToConstraint property.

[Arnagos]

Note: The reason we cannot use unique constraints instead of unique indices is the filter that we can apply on indices in MSSQL. This is not possible on constraints.

[martinjw]

RefersToConstraint generally refers to the primary key constraint of the other table. It can also refer to a unique constraint.

For sql server and PostgreSql, we get it from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS column unique_constraint_name, in Oracle it's all_constraints.r_constraint_name and so on. In other words, the metadata gives us the constraint names, not the columns on the other table. Normally that's good

So what happens when you reference a index? Is the index name there? That could be workable, if we can't match a PK or UK name, check the indexes. Can you check select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME= your_fk_constraint

[Arnagos]

Doesn't return anything. Removing the where clause shows that INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS only contains primary keys in my db.

As a note: I do have the correct columns as DBColumns available when I'm creating the new constraint. Getting the DbIndex would be possible as well.

[martinjw]

Self referencing table for testing.

CREATE TABLE [Diner]
(
  [Id] INT IDENTITY(1,1) NOT NULL,
  [Name] NVARCHAR (50)  NOT NULL,
  [Chef] NVARCHAR (50)
);
ALTER TABLE [Diner] ADD CONSTRAINT [PK_Diner] PRIMARY KEY ([Id]);

CREATE UNIQUE NONCLUSTERED INDEX [Idx_Diner_Name] ON [Diner]([Name]);

ALTER TABLE [Diner]  ADD CONSTRAINT [FK_Diner_Chef] FOREIGN KEY([Chef])
REFERENCES [Diner] ([Name]);

select UNIQUE_CONSTRAINT_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where CONSTRAINT_NAME='FK_Diner_Chef';

The unique constraint name is Idx_Diner_Name- this is good. We don't have to create another query to look for fks without constraints. I think chnaging DatabaseConstraint.ReferencedColumns logic should be enough

[Arnagos]

Thanks for the fix! It seems there is another problem related to this one though.

Loading the, now successfully created, constraint results in an object without any of the "RefersTo" properties. This of course leads to an endless migration because it always thinks that something has changed.

[Arnagos]

Could you upload the new version to NuGet?
As a general question: Is there a reason why you don't upload the pre-releases to NuGet (marked as pre-release of course)?

[martinjw]

Nuget published. The nuget releases are a manual process for me, I could change the workflow to publish pre-release nugets but I haven't got around to it :)

The RefersTo... properties should all be populated, with pks, fks and indexes - if you can create some simple ddl that repros it, please post it. There must be a scenario that information_schema is missing.