Java > Open Source Codes > org > hsqldb > Table


1 /* Copyright (c) 1995-2000, The Hypersonic SQL Group.
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the Hypersonic SQL Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  *
30  * This software consists of voluntary contributions made by many individuals
31  * on behalf of the Hypersonic SQL Group.
32  *
33  *
34  * For work added by the HSQL Development Group:
35  *
36  * Copyright (c) 2001-2005, The HSQL Development Group
37  * All rights reserved.
38  *
39  * Redistribution and use in source and binary forms, with or without
40  * modification, are permitted provided that the following conditions are met:
41  *
42  * Redistributions of source code must retain the above copyright notice, this
43  * list of conditions and the following disclaimer.
44  *
45  * Redistributions in binary form must reproduce the above copyright notice,
46  * this list of conditions and the following disclaimer in the documentation
47  * and/or other materials provided with the distribution.
48  *
49  * Neither the name of the HSQL Development Group nor the names of its
50  * contributors may be used to endorse or promote products derived from this
51  * software without specific prior written permission.
52  *
53  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
54  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
55  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
56  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
57  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
58  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
59  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
60  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
61  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
62  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
63  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
64  */
65
66
67 package org.hsqldb;
68
69 import java.io.IOException ;
70
71 import org.hsqldb.HsqlNameManager.HsqlName;
72 import org.hsqldb.index.RowIterator;
73 import org.hsqldb.lib.ArrayUtil;
74 import org.hsqldb.lib.HashMappedList;
75 import org.hsqldb.lib.HashSet;
76 import org.hsqldb.lib.HsqlArrayList;
77 import org.hsqldb.lib.Iterator;
78 import org.hsqldb.lib.StringUtil;
79 import org.hsqldb.persist.CachedObject;
80 import org.hsqldb.persist.DataFileCache;
81 import org.hsqldb.persist.PersistentStore;
82 import org.hsqldb.rowio.RowInputInterface;
83 import org.hsqldb.store.ValuePool;
84
85 // fredt@users 20020130 - patch 491987 by jimbag@users - made optional
86 // fredt@users 20020405 - patch 1.7.0 by fredt - quoted identifiers
87 // for sql standard quoted identifiers for column and table names and aliases
88 // applied to different places
89 // fredt@users 20020225 - patch 1.7.0 - restructuring
90 // some methods moved from Database.java, some rewritten
91 // changes to several methods
92 // fredt@users 20020225 - patch 1.7.0 - ON DELETE CASCADE
93 // fredt@users 20020225 - patch 1.7.0 - named constraints
94 // boucherb@users 20020225 - patch 1.7.0 - multi-column primary keys
95 // fredt@users 20020221 - patch 513005 by sqlbob@users (RMP)
96 // tony_lai@users 20020820 - patch 595099 - user defined PK name
97 // tony_lai@users 20020820 - patch 595172 - drop constraint fix
98 // kloska@users 20021030 - patch 1.7.2 - ON UPDATE CASCADE | SET NULL | SET DEFAULT
99 // kloska@users 20021112 - patch 1.7.2 - ON DELETE SET NULL | SET DEFAULT
100 // fredt@users 20021210 - patch 1.7.2 - better ADD / DROP INDEX for non-CACHED tables
101 // fredt@users 20030901 - patch 1.7.2 - allow multiple nulls for UNIQUE columns
102 // fredt@users 20030901 - patch 1.7.2 - reworked IDENTITY support
103 // achnettest@users 20040130 - patch 878288 - bug fix for new indexes in memory tables by Arne Christensen
104 // boucherb@users 20040327 - doc 1.7.2 - javadoc updates
105 // boucherb@users 200404xx - patch 1.7.2 - proper uri for getCatalogName
106 // fredt@users 20050000 - 1.8.0 updates in several areas
107 // fredt@users 20050220 - patch 1.8.0 enforcement of DECIMAL precision/scale
108
109 /**
110  * Holds the data structures and methods for creation of a database table.
111  *
112  *
113  * Extensively rewritten and extended in successive versions of HSQLDB.
114  *
115  * @author Thomas Mueller (Hypersonic SQL Group)
116  * @version 1.8.0
117  * @since Hypersonic SQL
118  */
119 public class Table extends BaseTable {
120
121     // types of table
122 public static final int SYSTEM_TABLE = 0;
123     public static final int SYSTEM_SUBQUERY = 1;
124     public static final int TEMP_TABLE = 2;
125     public static final int MEMORY_TABLE = 3;
126     public static final int CACHED_TABLE = 4;
127     public static final int TEMP_TEXT_TABLE = 5;
128     public static final int TEXT_TABLE = 6;
129     public static final int VIEW = 7;
130
131 // boucherb@users - for future implementation of SQL standard INFORMATION_SCHEMA
132 static final int SYSTEM_VIEW = 8;
133
134     // main properties
135 // boucherb@users - access changed in support of metadata 1.7.2
136 public HashMappedList columnList; // columns in table
137 private int[] primaryKeyCols; // column numbers for primary key
138 private int[] primaryKeyTypes; // types for primary key
139 private int[] primaryKeyColsSequence; // {0,1,2,...}
140 int[] bestRowIdentifierCols; // column set for best index
141 boolean bestRowIdentifierStrict; // true if it has no nullable column
142 int[] bestIndexForColumn; // index of the 'best' index for each column
143 Index bestIndex; // the best index overall - null if there is no user-defined index
144 int identityColumn; // -1 means no such row
145 NumberSequence identitySequence; // next value of identity column
146 NumberSequence rowIdSequence; // next value of optional rowid
147
148 // -----------------------------------------------------------------------
149 Constraint[] constraintList; // constrainst for the table
150 HsqlArrayList[] triggerLists; // array of trigger lists
151 private int[] colTypes; // fredt - types of columns
152 private int[] colSizes; // fredt - copy of SIZE values for columns
153 private int[] colScales; // fredt - copy of SCALE values for columns
154 private boolean[] colNullable; // fredt - modified copy of isNullable() values
155 private Expression[] colDefaults; // fredt - expressions of DEFAULT values
156 private int[] defaultColumnMap; // fred - holding 0,1,2,3,...
157 private boolean hasDefaultValues; //fredt - shortcut for above
158 boolean sqlEnforceSize; // inherited from the database -
159
160     // properties for subclasses
161 protected int columnCount; // inclusive the hidden primary key
162 public Database database;
163     protected DataFileCache cache;
164     protected HsqlName tableName; // SQL name
165 private int tableType;
166     protected boolean isReadOnly;
167     protected boolean isTemp;
168     protected boolean isCached;
169     protected boolean isText;
170     protected boolean isMemory;
171     private boolean isView;
172     protected boolean isLogged;
173     protected int indexType; // fredt - type of index used
174 protected boolean onCommitPreserve; // for temp tables
175
176     //
177 PersistentStore rowStore;
178     Index[] indexList; // vIndex(0) is the primary key index
179
180     /**
181      * Constructor
182      *
183      * @param db
184      * @param name
185      * @param type
186      * @param sessionid
187      * @exception HsqlException
188      */
189     Table(Database db, HsqlName name, int type) throws HsqlException {
190
191         database = db;
192         sqlEnforceSize = db.sqlEnforceStrictSize;
193         identitySequence = new NumberSequence(null, 0, 1, Types.BIGINT);
194         rowIdSequence = new NumberSequence(null, 0, 1, Types.BIGINT);
195
196         switch (type) {
197
198             case SYSTEM_SUBQUERY :
199                 isTemp = true;
200                 isMemory = true;
201             case SYSTEM_TABLE :
202                 isMemory = true;
203                 break;
204
205             case CACHED_TABLE :
206                 if (DatabaseURL.isFileBasedDatabaseType(db.getType())) {
207                     cache = db.logger.getCache();
208                     isCached = true;
209                     isLogged = !database.isFilesReadOnly();
210                     indexType = Index.DISK_INDEX;
211                     rowStore = new RowStore();
212
213                     break;
214                 }
215
216                 type = MEMORY_TABLE;
217             case MEMORY_TABLE :
218                 isMemory = true;
219                 isLogged = !database.isFilesReadOnly();
220                 break;
221
222             case TEMP_TABLE :
223                 isMemory = true;
224                 isTemp = true;
225                 break;
226
227             case TEMP_TEXT_TABLE :
228                 if (!DatabaseURL.isFileBasedDatabaseType(db.getType())) {
229                     throw Trace.error(Trace.DATABASE_IS_MEMORY_ONLY);
230                 }
231
232                 isTemp = true;
233                 isText = true;
234                 isReadOnly = true;
235                 indexType = Index.POINTER_INDEX;
236                 rowStore = new RowStore();
237                 break;
238
239             case TEXT_TABLE :
240                 if (!DatabaseURL.isFileBasedDatabaseType(db.getType())) {
241                     throw Trace.error(Trace.DATABASE_IS_MEMORY_ONLY);
242                 }
243
244                 isText = true;
245                 indexType = Index.POINTER_INDEX;
246                 rowStore = new RowStore();
247                 break;
248
249             case VIEW :
250             case SYSTEM_VIEW :
251                 isView = true;
252                 break;
253         }
254
255         // type may have changed above for CACHED tables
256 tableType = type;
257         tableName = name;
258         primaryKeyCols = null;
259         primaryKeyTypes = null;
260         identityColumn = -1;
261         columnList = new HashMappedList();
262         indexList = new Index[0];
263         constraintList = new Constraint[0];
264         triggerLists = new HsqlArrayList[TriggerDef.NUM_TRIGS];
265
266 // ----------------------------------------------------------------------------
267 // akede@users - 1.7.2 patch Files readonly
268 // Changing the mode of the table if necessary
269 if (db.isFilesReadOnly() && isFileBased()) {
270             this.isReadOnly = true;
271         }
272
273 // ----------------------------------------------------------------------------
274 }
275
276     boolean equals(Session session, String name) {
277
278 /*
279         if (isTemp && (session != null
280                        && session.getId() != ownerSessionId)) {
281             return false;
282         }
283 */
284         return (tableName.name.equals(name));
285     }
286
287     boolean equals(String name) {
288         return (tableName.name.equals(name));
289     }
290
291     boolean equals(HsqlName name) {
292         return (tableName.equals(name));
293     }
294
295     public final boolean isText() {
296         return isText;
297     }
298
299     public final boolean isTemp() {
300         return isTemp;
301     }
302
303     public final boolean isReadOnly() {
304         return isReadOnly;
305     }
306
307     final boolean isView() {
308         return isView;
309     }
310
311     final int getIndexType() {
312         return indexType;
313     }
314
315     public final int getTableType() {
316         return tableType;
317     }
318
319     public final boolean isDataReadOnly() {
320         return isReadOnly;
321     }
322
323     /**
324      * Used by INSERT, DELETE, UPDATE operations
325      */
326     void checkDataReadOnly() throws HsqlException {
327
328         if (isReadOnly) {
329             throw Trace.error(Trace.DATA_IS_READONLY);
330         }
331     }
332
333 // ----------------------------------------------------------------------------
334 // akede@users - 1.7.2 patch Files readonly
335 void setDataReadOnly(boolean value) throws HsqlException {
336
337         // Changing the Read-Only mode for the table is only allowed if the
338 // the database can realize it.
339 if (!value && database.isFilesReadOnly() && isFileBased()) {
340             throw Trace.error(Trace.DATA_IS_READONLY);
341         }
342
343         isReadOnly = value;
344     }
345
346     /**
347      * Text or Cached Tables are normally file based
348      */
349     boolean isFileBased() {
350         return isCached || isText;
351     }
352
353     /**
354      * For text tables
355      */
356     protected void setDataSource(Session s, String source, boolean isDesc,
357                                  boolean newFile) throws HsqlException {
358         throw (Trace.error(Trace.TABLE_NOT_FOUND));
359     }
360
361     /**
362      * For text tables
363      */
364     protected String getDataSource() {
365         return null;
366     }
367
368     /**
369      * For text tables.
370      */
371     protected boolean isDescDataSource() {
372         return false;
373     }
374
375     /**
376      * For text tables.
377      */
378     public void setHeader(String header) throws HsqlException {
379         throw Trace.error(Trace.TEXT_TABLE_HEADER);
380     }
381
382     /**
383      * For text tables.
384      */
385     public String getHeader() {
386         return null;
387     }
388
389     /**
390      * Adds a constraint.
391      */
392     void addConstraint(Constraint c) {
393
394         constraintList =
395             (Constraint[]) ArrayUtil.toAdjustedArray(constraintList, c,
396                 constraintList.length, 1);
397     }
398
399     /**
400      * Adds a constraint.
401      */
402     void addPKConstraint(Constraint c) {
403         constraintList =
404             (Constraint[]) ArrayUtil.toAdjustedArray(constraintList, c, 0, 1);
405     }
406
407     /**
408      * Returns the list of constraints.
409      */
410     Constraint[] getConstraints() {
411         return constraintList;
412     }
413
414     /**
415      * Returns the primary constraint.
416      */
417     Constraint getPrimaryConstraint() {
418         return primaryKeyCols.length == 0 ? null
419                                           : constraintList[0];
420     }
421
422 /** @todo fredt - this can be improved to ignore order of columns in
423      * multi-column indexes */
424
425     /**
426      * Returns the index supporting a constraint with the given column signature.
427      * Only Unique constraints are considered.
428      */
429     Index getUniqueConstraintIndexForColumns(int[] col) {
430
431         if (ArrayUtil.areEqual(getPrimaryIndex().getColumns(), col,
432                                col.length, true)) {
433             return getPrimaryIndex();
434         }
435
436         for (int i = 0, size = constraintList.length; i < size; i++) {
437             Constraint c = constraintList[i];
438
439             if (c.getType() != Constraint.UNIQUE) {
440                 continue;
441             }
442
443             if (ArrayUtil.areEqual(c.getMainColumns(), col, col.length,
444                                    true)) {
445                 return c.getMainIndex();
446             }
447         }
448
449         return null;
450     }
451
452     /**
453      * Returns any foreign key constraint equivalent to the column sets
454      */
455     Constraint getConstraintForColumns(Table tablemain, int[] colmain,
456                                        int[] colref) {
457
458         for (int i = 0, size = constraintList.length; i < size; i++) {
459             Constraint c = constraintList[i];
460
461             if (c.isEquivalent(tablemain, colmain, this, colref)) {
462                 return c;
463             }
464         }
465
466         return null;
467     }
468
469     /**
470      * Returns any unique constraint equivalent to the column set
471      */
472     Constraint getUniqueConstraintForColumns(int[] cols) {
473
474         for (int i = 0, size = constraintList.length; i < size; i++) {
475             Constraint c = constraintList[i];
476
477             if (c.isEquivalent(cols, Constraint.UNIQUE)) {
478                 return c;
479             }
480         }
481
482         return null;
483     }
484
485     /**
486      * Returns any unique Constraint using this index
487      *
488      * @param index
489      * @return
490      */
491     Constraint getUniqueOrPKConstraintForIndex(Index index) {
492
493         for (int i = 0, size = constraintList.length; i < size; i++) {
494             Constraint c = constraintList[i];
495
496             if (c.getMainIndex() == index
497                     && (c.getType() == Constraint.UNIQUE
498                         || c.getType() == Constraint.PRIMARY_KEY)) {
499                 return c;
500             }
501         }
502
503         return null;
504     }
505
506     /**
507      * Returns the next constraint of a given type
508      *
509      * @param from
510      * @param type
511      * @return
512      */
513     int getNextConstraintIndex(int from, int type) {
514
515         for (int i = from, size = constraintList.length; i < size; i++) {
516             Constraint c = constraintList[i];
517
518             if (c.getType() == type) {
519                 return i;
520             }
521         }
522
523         return -1;
524     }
525
526 // fredt@users 20020220 - patch 475199 - duplicate column
527
528     /**
529      * Performs the table level checks and adds a column to the table at the
530      * DDL level. Only used at table creation, not at alter column.
531      */
532     void addColumn(Column column) throws HsqlException {
533
534         if (findColumn(column.columnName.name) >= 0) {
535             throw Trace.error(Trace.COLUMN_ALREADY_EXISTS);
536         }
537
538         if (column.isIdentity()) {
539             Trace.check(
540                 column.getType() == Types.INTEGER
541                 || column.getType() == Types.BIGINT, Trace.WRONG_DATA_TYPE,
542                     column.columnName.name);
543             Trace.check(identityColumn == -1, Trace.SECOND_PRIMARY_KEY,
544                         column.columnName.name);
545
546             identityColumn = columnCount;
547         }
548
549         if (primaryKeyCols != null) {
550             Trace.doAssert(false, "Table.addColumn");
551         }
552
553         columnList.add(column.columnName.name, column);
554
555         columnCount++;
556     }
557
558     /**
559      * Add a set of columns based on a ResultMetaData
560      */
561     void addColumns(Result.ResultMetaData metadata,
562                     int count) throws HsqlException {
563
564         for (int i = 0; i < count; i++) {
565             Column column = new Column(
566                 database.nameManager.newHsqlName(
567                     metadata.colLabels[i], metadata.isLabelQuoted[i]), true,
568                         metadata.colTypes[i], metadata.colSizes[i],
569                         metadata.colScales[i], false, null);
570
571             addColumn(column);
572         }
573     }
574
575     /**
576      * Adds a set of columns based on a compiled Select
577      */
578     void addColumns(Select select) throws HsqlException {
579
580         int colCount = select.iResultLen;
581
582         for (int i = 0; i < colCount; i++) {
583             Expression e = select.exprColumns[i];
584             Column column = new Column(
585                 database.nameManager.newHsqlName(
586                     e.getAlias(), e.isAliasQuoted()), true, e.getDataType(),
587                         e.getColumnSize(), e.getColumnScale(), false, null);
588
589             addColumn(column);
590         }
591     }
592
593     /**
594      * Returns the HsqlName object fo the table
595      */
596     public HsqlName getName() {
597         return tableName;
598     }
599
600     public int getId() {
601         return tableName.hashCode();
602     }
603
604     /**
605      * Changes table name. Used by 'alter table rename to'.
606      * Essential to use the existing HsqlName as this is is referenced by
607      * intances of Constraint etc.
608      */
609     void rename(Session session, String newname,
610                 boolean isquoted) throws HsqlException {
611
612         String oldname = tableName.name;
613
614         tableName.rename(newname, isquoted);
615
616         if (HsqlName.isReservedIndexName(getPrimaryIndex().getName().name)) {
617             getPrimaryIndex().getName().rename("SYS_PK", newname, isquoted);
618         }
619
620         renameTableInCheckConstraints(session, oldname, newname);
621     }
622
623     /**
624      * Returns total column counts, including hidden ones.
625      */
626     int getInternalColumnCount() {
627         return columnCount;
628     }
629
630     /**
631      * returns a basic duplicate of the table without the data structures.
632      */
633     protected Table duplicate() throws HsqlException {
634
635         Table t = (new Table(database, tableName, tableType));
636
637         return t;
638     }
639
640     /**
641      * Match two columns arrays for length and type of columns
642      *
643      * @param col column array from this Table
644      * @param other the other Table object
645      * @param othercol column array from the other Table
646      * @throws HsqlException if there is a mismatch
647      */
648     void checkColumnsMatch(int[] col, Table other,
649                            int[] othercol) throws HsqlException {
650
651         if (col.length != othercol.length) {
652             throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
653         }
654
655         for (int i = 0; i < col.length; i++) {
656
657             // integrity check - should not throw in normal operation
658 if (col[i] >= columnCount || othercol[i] >= other.columnCount) {
659                 throw Trace.error(Trace.COLUMN_COUNT_DOES_NOT_MATCH);
660             }
661
662             if (getColumn(col[i]).getType()
663                     != other.getColumn(othercol[i]).getType()) {
664                 throw Trace.error(Trace.COLUMN_TYPE_MISMATCH);
665             }
666         }
667     }
668
669 // fredt@users 20020405 - patch 1.7.0 by fredt - DROP and CREATE INDEX bug
670
671     /**
672      * Constraints that need removing are removed outside this method.<br>
673      * removeIndex is the index of an index to be removed, in which case
674      * no change is made to columns <br>
675      * When withoutindex is null, adjust {-1 | 0 | +1} indicates if a
676      * column is {removed | replaced | added}
677      *
678      */
679     Table moveDefinition(int[] removeIndex, Column newColumn, int colIndex,
680                          int adjust) throws HsqlException {
681
682         Table tn = duplicate();
683
684         // loop beyond the end in order to be able to add a column to the end
685 // of the list
686 for (int i = 0; i < columnCount + 1; i++) {
687             if (i == colIndex) {
688                 if (adjust == 0) {
689                     if (newColumn != null) {
690                         tn.addColumn(newColumn);
691
692                         continue;
693                     }
694                 } else if (adjust > 0) {
695                     tn.addColumn(newColumn);
696                 } else if (adjust < 0) {
697                     continue;
698                 }
699             }
700
701             if (i == columnCount) {
702                 break;
703             }
704
705             tn.addColumn(getColumn(i));
706         }
707
708         // treat it the same as new table creation and
709 int[] primarykey = primaryKeyCols.length == 0 ? null
710                                                       : primaryKeyCols;
711
712         if (primarykey != null) {
713             int[] newpk = ArrayUtil.toAdjustedColumnArray(primarykey,
714                 colIndex, adjust);
715
716             if (primarykey.length != newpk.length) {
717                 throw Trace.error(Trace.DROP_PRIMARY_KEY);
718             } else {
719                 primarykey = newpk;
720             }
721         }
722
723         tn.createPrimaryKey(getIndex(0).getName(), primarykey, false);
724
725         tn.constraintList = constraintList;
726
727         Index idx = null;
728
729         if (removeIndex != null) {
730             idx = getIndex(removeIndex, colIndex);
731         }
732
733         if (idx != null) {
734             if (idx.isConstraint()) {
735                 throw Trace.error(Trace.COLUMN_IS_IN_CONSTRAINT);
736             } else {
737                 throw Trace.error(Trace.COLUMN_IS_IN_INDEX);
738             }
739         }
740
741         for (int i = 1; i < indexList.length; i++) {
742             if (removeIndex != null && ArrayUtil.find(removeIndex, i) != -1) {
743                 continue;
744             }
745
746             tn.createAdjustedIndex(indexList[i], colIndex, adjust);
747         }
748
749         tn.triggerLists = triggerLists;
750
751         return tn;
752     }
753
754     Index getIndex(int[] exclude, int colIndex) {
755
756         for (int i = 1; i < indexList.length; i++) {
757             if (exclude != null && ArrayUtil.find(exclude, i) != -1) {
758                 continue;
759             }
760
761             Index idx = indexList[i];
762             int[] cols = idx.getColumns();
763
764             if (ArrayUtil.find(cols, colIndex) != -1) {
765                 return idx;
766             }
767         }
768
769         return null;
770     }
771
772     private void copyIndexes(Table tn, int removeIndex, int colIndex,
773                              int adjust) throws HsqlException {
774
775         for (int i = 1; i < getIndexCount(); i++) {
776             Index idx = indexList[i];
777
778             if (removeIndex == i) {
779                 continue;
780             }
781
782             Index newidx = tn.createAdjustedIndex(idx, colIndex, adjust);
783
784             if (newidx == null) {
785
786                 // column to remove is part of an index
787 throw Trace.error(Trace.COLUMN_IS_IN_INDEX);
788             }
789         }
790     }
791
792     /**
793      * cols == null means drop
794      */
795     Table moveDefinitionPK(int[] pkCols,
796                            boolean withIdentity) throws HsqlException {
797
798         // some checks
799 if ((hasPrimaryKey() && pkCols != null)
800                 || (!hasPrimaryKey() && pkCols == null)) {
801             throw Trace.error(Trace.DROP_PRIMARY_KEY);
802         }
803
804         Table tn = duplicate();
805
806         for (int i = 0; i < columnCount; i++) {
807             tn.addColumn(getColumn(i).duplicate(withIdentity));
808         }
809
810         tn.createPrimaryKey(getIndex(0).getName(), pkCols, true);
811
812         tn.constraintList = constraintList;
813
814         for (int i = 1; i < getIndexCount(); i++) {
815             Index idx = getIndex(i);
816
817             tn.createAdjustedIndex(idx, -1, 0);
818         }
819
820         tn.triggerLists = triggerLists;
821
822         return tn;
823     }
824
825     /**
826      * Updates the constraint and replaces references to the old table with
827      * the new one, adjusting column index arrays by the given amount.
828      */
829     void updateConstraintsTables(Session session, Table old, int colindex,
830                                  int adjust) throws HsqlException {
831
832         for (int i = 0, size = constraintList.length; i < size; i++) {
833             Constraint c = constraintList[i];
834
835             c.replaceTable(old, this, colindex, adjust);
836
837             if (c.constType == Constraint.CHECK) {
838                 recompileCheckConstraint(session, c);
839             }
840         }
841     }
842
843     private void recompileCheckConstraints(Session session)
844     throws HsqlException {
845
846         for (int i = 0, size = constraintList.length; i < size; i++) {
847             Constraint c = constraintList[i];
848
849             if (c.constType == Constraint.CHECK) {
850                 recompileCheckConstraint(session, c);
851             }
852         }
853     }
854
855     /**
856      * Used after adding columns or indexes to the table.
857      */
858     private void recompileCheckConstraint(Session session,
859                                           Constraint c) throws HsqlException {
860
861         String ddl = c.core.check.getDDL();
862         Tokenizer tokenizer = new Tokenizer(ddl);
863         Parser parser = new Parser(session, database, tokenizer);
864         Expression condition = parser.parseExpression();
865
866         c.core.check = condition;
867
868         // this workaround is here to stop LIKE optimisation (for proper scripting)
869 condition.setLikeOptimised();
870
871         Select s = Expression.getCheckSelect(session, this, condition);
872
873         c.core.checkFilter = s.tFilter[0];
874
875         c.core.checkFilter.setAsCheckFilter();
876
877         c.core.mainTable = this;
878     }
879
880     /**
881      * Used for drop column.
882      */
883     void checkColumnInCheckConstraint(String colname) throws HsqlException {
884
885         for (int i = 0, size = constraintList.length; i < size; i++) {
886             Constraint c = constraintList[i];
887
888             if (c.constType == Constraint.CHECK) {
889                 if (c.hasColumn(this, colname)) {
890                     throw Trace.error(Trace.COLUMN_IS_REFERENCED,
891                                       c.getName());
892                 }
893             }
894         }
895     }
896
897     /**
898      * Used for retype column. Checks whether column is in an FK or is
899      * referenced by a FK
900      * @param colIndex index
901      */
902     void checkColumnInFKConstraint(int colIndex) throws HsqlException {
903
904         for (int i = 0, size = constraintList.length; i < size; i++) {
905             Constraint c = constraintList[i];
906
907             if (c.hasColumn(colIndex)
908                     && (c.getType() == Constraint.MAIN
909                         || c.getType() == Constraint.FOREIGN_KEY)) {
910                 throw Trace.error(Trace.COLUMN_IS_REFERENCED,
911                                   c.getName().name);
912             }
913         }
914     }
915
916     /**
917      * Used for column defaults and nullability. Checks whether column is in an FK.
918      * @param colIndex index of column
919      * @param refOnly only check FK columns, not referenced columns
920      */
921     void checkColumnInFKConstraint(int colIndex,
922                                    int actionType) throws HsqlException {
923
924         for (int i = 0, size = constraintList.length; i < size; i++) {
925             Constraint c = constraintList[i];
926
927             if (c.hasColumn(colIndex)) {
928                 if (c.getType() == Constraint.FOREIGN_KEY
929                         && (actionType == c.getUpdateAction()
930                             || actionType == c.getDeleteAction())) {
931                     throw Trace.error(Trace.COLUMN_IS_REFERENCED,
932                                       c.getName().name);
933                 }
934             }
935         }
936     }
937
938     /**
939      * Used for rename column.
940      */
941     private void renameColumnInCheckConstraints(String oldname,
942