Yes, that's interesting. SQL Server doesn't seem to consider unique indexes as constraints... I have worked a similar query to the one you've suggested into jOOQ-meta, which fixes the issue.
Thanks again for reporting! Lukas 2014-02-27 18:17 GMT+01:00 <[email protected]>: > Lukas, > > I believe the issue is more pervasive than that in SqlServer2008. Any > unique index created using the syntax: > > CREATE UNIQUE NONCLUSTERED INDEX MYTABLE_UK_01 ON MYTABLE (MY_COLUMN) > instead of > ALTER TABLE MYTABLE ADD CONSTRAINT MYTABLE_UK_01 UNIQUE NONCLUSTERED > (MY_COLUMN) > is not found. > > Craig > > > On Thursday, February 27, 2014 2:20:24 AM UTC-5, Lukas Eder wrote: > >> Hi Craig, >> >> Thank you for this contribution. I have created an issue for this: >> https://github.com/jOOQ/jOOQ/issues/3084 >> >> I will investigate your findings and see if we can work a fix into jOOQ >> 3.4.0, 3.3.1, and 3.2.4. >> Do you think that your index is not found because it is a partial index? >> >> Cheers >> Lukas >> >> >> 2014-02-26 17:30 GMT+01:00 <[email protected]>: >> >>> I have a filtered unique index in SQLServer2008 that is not found by >>> jooq 3.1.0. It is declared using this syntax: >>> >>> CREATE UNIQUE NONCLUSTERED INDEX MYTABLE_UK_01 ON dbo.[MYTABLE](MY_COLUMN) >>> WHERE MY_COLUMN IS NOT NULL; >>> >>> I have used the following query to find all UKs. Perhaps it will be of >>> use to others: >>> >>> SELECT >>> Table_Name = t.name, >>> Index_Name = ind.name, >>> Column_Name = col.name, >>> Key_Ordinal = ic.key_ordinal, >>> Schema_Name = s.name >>> FROM >>> sys.indexes ind >>> INNER JOIN >>> sys.index_columns ic ON ind.object_id = ic.object_id and ind. >>> index_id = ic.index_id >>> INNER JOIN >>> sys.columns col ON ic.object_id = col.object_id and ic.column_id = >>> col.column_id >>> INNER JOIN >>> sys.tables t ON ind.object_id = t.object_id >>> INNER JOIN >>> sys.schemas s ON t.schema_id = s.schema_id >>> WHERE >>> ind.is_primary_key = 0 >>> AND ind.is_unique = 1 >>> ORDER BY >>> t.name, ind.name, ind.index_id, ic.key_ordinal >>> >>> Not sure if it is completely accurate, but it finds everything in my >>> database. >>> >>> Then i have to override the loadUniqueKeys(DefaultRelations relations) >>> method. >>> >>> *protected void* loadUniqueKeys(DefaultRelations relations) *throws >>> *SQLException >>> { >>> for (Record record : create().fetch(UK_QUERY)) { >>> SchemaDefinition schema = getSchema((String)record. >>> getValue("Schema_Name")); >>> String tableName = (String)record.getValue("Table_Name"); >>> TableDefinition table = getTable(schema, tableName); >>> String columnName = (String)record.getValue("Column_Name"); >>> ColumnDefinition column = table.getColumn(columnName); >>> String key = (String)record.getValue("Index_Name"); >>> relations.addUniqueKey(key, column); >>> } >>> } >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "jOOQ User Group" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >> >> >> -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
