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.
