Provide information about primary keys from ODBC GetSchema()

[MaceWindu]

Request, similar to #28517

Right now it is possible to get names and columns for primary keys, foreign keys and indexes for ODBC using Indexes schema table, but it is not possible to tell wether it actually PK, FK or Index.

This creates problem when you need this information from Access database, as GetSchema is the only way to get schema information for it. Most of ther databases allow you to query INFORMATION_SCHEMA or similar sources of schema information.

FYI, this information available over OleDb provider.

E.g. following test table (MS Access):

CREATE TABLE RelationsTable
(
	ID1		INT NOT NULL,
	ID2		INT NOT NULL,
	Int1	INT NOT NULL,
	Int2	INT NOT NULL,
	IntN1	INT NULL,
	IntN2	INT NULL,
	FK		INT NOT NULL,
	FKN		INT NULL
)
GO
CREATE INDEX PK_RelationsTable ON RelationsTable(ID1, ID2) WITH PRIMARY;
GO
CREATE INDEX IX_Index ON RelationsTable(Int1, IntN1);
GO
CREATE UNIQUE INDEX UX_Index1 ON RelationsTable(Int1);
GO
CREATE UNIQUE INDEX UX_Index2 ON RelationsTable(IntN1);
GO
ALTER TABLE RelationsTable ADD CONSTRAINT FK_Nullable FOREIGN KEY (IntN1, IntN2) REFERENCES RelationsTable(ID1, ID2);
GO
ALTER TABLE RelationsTable ADD CONSTRAINT FK_NotNullable FOREIGN KEY (Int1, Int2) REFERENCES RelationsTable(ID1, ID2);
GO

produce following data in Indexes table:

As you can see there is no way to distinguish index types. There is even some TYPE column, but it contains same obscure 3 value for all records.

[roji]

As in #35444, are you using this info returned from other, non-Access ODBC providers?

[dotnet-policy-service]

Due to lack of recent activity, this issue has been marked as a candidate for backlog cleanup. It will be closed if no further activity occurs within 14 more days. Any new comment (by anyone, not necessarily the author) will undo this process.

This process is part of our issue cleanup automation.

[dotnet-policy-service]

This issue will now be closed since it had been marked no-recent-activity but received no further activity in the past 14 days. It is still possible to reopen or comment on the issue, but please note that the issue will be locked if it remains inactive for another 30 days.