Problem AND SOLUTION: Duplicate PK or UK columns MSSQL with schema

Midnight posted 5 months ago in General

In MS SQL, if a table has a schema, the columns in primary or unique keys are duplicated. This shows up in view and table design. [E.g. if column ID is the primary key, it will show up as ID, ID]

Possible solution: This requires a tweak to HeidiSQL. In the SQL to generate the table view, add another condition to one of the joins:

Change: "...ON C.CONSTRAINT_NAME = K.CONSTRAINT_NAME AND K.table_name =..."

To: "...ON C.CONSTRAINT_NAME = K.CONSTRAINT_NAME **AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA** AND K.table_name =..."

NB: This might also affect other indexes and foreign keys. (I have not checked these.)

Please login to leave a reply, or register at first.