Update of IINFORMATION_SCHEMA.KEY_COLUMN_USAGE Sql CE

Question

How do you ensure INFORMATION_SCHEMA.KEY_COLUMN_USAGE is updated after you have added/created a new index. ie:

CREATE UNIQUE INDEX [UK_Common.UserConnection]
   ON [Common.UserConnection] ([SessionId] ASC,[UserId] ASC);
GO

I have to databases, one is transformed/updated from an old version (V1) to a new version (V10) the other is a fresh new (V10) database. When I compare the databases everything matches (this includes PK, FK, Indexes etc) except:

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

I can see that my newly created uniq index ([UK_Common.UserConnection] SessionId and UserId) is missing in the transformed database. The transformed database counts to less rows in the "key_column_usage" table compared to the new (V10) database.

If I extract "Create sql table script" from any sql ce client application the script for the transformed/updated and new (V10) script is identical.

I have tried to include an insert into statement in the transformer:

    INSERT INTO [INFORMATION_SCHEMA.KEY_COLUMN_USAGE]
            (COL0,COL1,COL2,COL3)
    VALUES ('value0','value1','value2',value3)

    GO

Result: Data cannot be added to a system table or a schema information view. [ Name of read-only table = @@INFORMATION_SCHEMA.KEY_COLUMN_USAGE ]

I have also tried to code this inn .net where the select query (rot to count key error) is located.

("SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE")

Result: Data cannot be added to a system table or a schema information view. [ Name of read-only table = @@INFORMATION_SCHEMA.KEY_COLUMN_USAGE ]

Question is related to a failing unit test (mstest) in an asp.net project: Assert.IsTrue failed. Count of key column usages is different.

How do you update: INFORMATION_SCHEMA.KEY_COLUMN_USAGE after editing/change of INDEX?

.NET 4.0 SQL CE 4.0 VS2010

Answer 1

You cannot update this table (view), it will be updated by ms sql ce instance based on the use of the key. It's usually not such a good idea to modify the information_schema directly in any of the RDBMs. In most cases users are not allowed to do that anyway.

Edit: I do not think that differences in information_schema should be used as an exception criterion in testing.