private String getUniqueConstraintsSqlInformix ( InformixDatabase database , Schema schema , String name ) { StringBuffer sqlBuf = new StringBuffer ( ) ; sqlBuf . append ( "SELECT * FROM (\n" ) ; // Yes, I am serious about this. It appears there are neither CTE/WITH clauses nor PIVOT/UNPIVOT operators // in Informix SQL. for ( int i = 1 ; i <= 16 ; i ++ ) { if ( i > 1 ) sqlBuf . append ( "UNION ALL\n" ) ; sqlBuf . append ( String . format ( " SELECT\n" + " CONS.owner,\n" + " CONS.constrname AS constraint_name,\n" + " COL.colname AS column_name,\n" + " CONS.constrtype,\n" + " %d AS column_index\n" + " FROM informix.sysconstraints CONS\n" + " JOIN informix.sysindexes IDX ON CONS.idxname = IDX.idxname\n" + " JOIN informix.syscolumns COL ON COL.tabid = CONS.tabid AND COL.colno = IDX.part%d\n" , i , i ) ) ; } // Finish the subquery and filter on the U(NIQUE) constraint type sqlBuf . append ( " ) SUBQ\n" + "WHERE constrtype='U' \n" ) ; String catalogName = database . correctObjectName ( schema . getCatalogName ( ) , Catalog . class ) ; String constraintName = database . correctObjectName ( name , UniqueConstraint . class ) ; // If possible, filter for catalog name and/or constraint name if ( catalogName != null ) { sqlBuf . append ( "AND owner='" + catalogName + "'\n" ) ; } if ( constraintName != null ) { sqlBuf . append ( "AND constraint_name='" + constraintName + "'" ) ; } // For correct processing, it is important that we get all columns in order. sqlBuf . append ( "ORDER BY owner, constraint_name, column_index" ) ; // Return the query return sqlBuf . toString ( ) ; }
Gets an SQL query that returns the constraint names and columns for all UNIQUE constraints .