Return to unique.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    1: # 2001 September 27
    2: #
    3: # The author disclaims copyright to this source code.  In place of
    4: # a legal notice, here is a blessing:
    5: #
    6: #    May you do good and not evil.
    7: #    May you find forgiveness for yourself and forgive others.
    8: #    May you share freely, never taking more than you give.
    9: #
   10: #***********************************************************************
   11: # This file implements regression tests for SQLite library.  The
   12: # focus of this file is testing the CREATE UNIQUE INDEX statement,
   13: # and primary keys, and the UNIQUE constraint on table columns
   14: #
   15: # $Id: unique.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
   16: 
   17: set testdir [file dirname $argv0]
   18: source $testdir/tester.tcl
   19: 
   20: # Try to create a table with two primary keys.
   21: # (This is allowed in SQLite even that it is not valid SQL)
   22: #
   23: do_test unique-1.1 {
   24:   catchsql {
   25:     CREATE TABLE t1(
   26:        a int PRIMARY KEY,
   27:        b int PRIMARY KEY,
   28:        c text
   29:     );
   30:   }
   31: } {1 {table "t1" has more than one primary key}}
   32: do_test unique-1.1b {
   33:   catchsql {
   34:     CREATE TABLE t1(
   35:        a int PRIMARY KEY,
   36:        b int UNIQUE,
   37:        c text
   38:     );
   39:   }
   40: } {0 {}}
   41: do_test unique-1.2 {
   42:   catchsql {
   43:     INSERT INTO t1(a,b,c) VALUES(1,2,3)
   44:   }
   45: } {0 {}}
   46: do_test unique-1.3 {
   47:   catchsql {
   48:     INSERT INTO t1(a,b,c) VALUES(1,3,4)
   49:   }
   50: } {1 {column a is not unique}}
   51: do_test unique-1.4 {
   52:   execsql {
   53:     SELECT * FROM t1 ORDER BY a;
   54:   }
   55: } {1 2 3}
   56: do_test unique-1.5 {
   57:   catchsql {
   58:     INSERT INTO t1(a,b,c) VALUES(3,2,4)
   59:   }
   60: } {1 {column b is not unique}}
   61: do_test unique-1.6 {
   62:   execsql {
   63:     SELECT * FROM t1 ORDER BY a;
   64:   }
   65: } {1 2 3}
   66: do_test unique-1.7 {
   67:   catchsql {
   68:     INSERT INTO t1(a,b,c) VALUES(3,4,5)
   69:   }
   70: } {0 {}}
   71: do_test unique-1.8 {
   72:   execsql {
   73:     SELECT * FROM t1 ORDER BY a;
   74:   }
   75: } {1 2 3 3 4 5}
   76: integrity_check unique-1.9
   77: 
   78: do_test unique-2.0 {
   79:   execsql {
   80:     DROP TABLE t1;
   81:     CREATE TABLE t2(a int, b int);
   82:     INSERT INTO t2(a,b) VALUES(1,2);
   83:     INSERT INTO t2(a,b) VALUES(3,4);
   84:     SELECT * FROM t2 ORDER BY a;
   85:   }
   86: } {1 2 3 4}
   87: do_test unique-2.1 {
   88:   catchsql {
   89:     CREATE UNIQUE INDEX i2 ON t2(a)
   90:   }
   91: } {0 {}}
   92: do_test unique-2.2 {
   93:   catchsql {
   94:     SELECT * FROM t2 ORDER BY a
   95:   }
   96: } {0 {1 2 3 4}}
   97: do_test unique-2.3 {
   98:   catchsql {
   99:     INSERT INTO t2 VALUES(1,5);
  100:   }
  101: } {1 {column a is not unique}}
  102: do_test unique-2.4 {
  103:   catchsql {
  104:     SELECT * FROM t2 ORDER BY a
  105:   }
  106: } {0 {1 2 3 4}}
  107: do_test unique-2.5 {
  108:   catchsql {
  109:     DROP INDEX i2;
  110:     SELECT * FROM t2 ORDER BY a;
  111:   }
  112: } {0 {1 2 3 4}}
  113: do_test unique-2.6 {
  114:   catchsql {
  115:     INSERT INTO t2 VALUES(1,5)
  116:   }
  117: } {0 {}}
  118: do_test unique-2.7 {
  119:   catchsql {
  120:     SELECT * FROM t2 ORDER BY a, b;
  121:   }
  122: } {0 {1 2 1 5 3 4}}
  123: do_test unique-2.8 {
  124:   catchsql {
  125:     CREATE UNIQUE INDEX i2 ON t2(a);
  126:   }
  127: } {1 {indexed columns are not unique}}
  128: do_test unique-2.9 {
  129:   catchsql {
  130:     CREATE INDEX i2 ON t2(a);
  131:   }
  132: } {0 {}}
  133: integrity_check unique-2.10
  134: 
  135: # Test the UNIQUE keyword as used on two or more fields.
  136: #
  137: do_test unique-3.1 {
  138:   catchsql {
  139:     CREATE TABLE t3(
  140:        a int,
  141:        b int,
  142:        c int,
  143:        d int,
  144:        unique(a,c,d)
  145:      );
  146:   }
  147: } {0 {}}
  148: do_test unique-3.2 {
  149:   catchsql {
  150:     INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4);
  151:     SELECT * FROM t3 ORDER BY a,b,c,d;
  152:   }
  153: } {0 {1 2 3 4}}
  154: do_test unique-3.3 {
  155:   catchsql {
  156:     INSERT INTO t3(a,b,c,d) VALUES(1,2,3,5);
  157:     SELECT * FROM t3 ORDER BY a,b,c,d;
  158:   }
  159: } {0 {1 2 3 4 1 2 3 5}}
  160: do_test unique-3.4 {
  161:   catchsql {
  162:     INSERT INTO t3(a,b,c,d) VALUES(1,4,3,5);
  163:     SELECT * FROM t3 ORDER BY a,b,c,d;
  164:   }
  165: } {1 {columns a, c, d are not unique}}
  166: integrity_check unique-3.5
  167: 
  168: # Make sure NULLs are distinct as far as the UNIQUE tests are
  169: # concerned.
  170: #
  171: do_test unique-4.1 {
  172:   execsql {
  173:     CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
  174:     INSERT INTO t4 VALUES(1,2,3);
  175:     INSERT INTO t4 VALUES(NULL, 2, NULL);
  176:     SELECT * FROM t4;
  177:   }
  178: } {1 2 3 {} 2 {}}
  179: do_test unique-4.2 {
  180:   catchsql {
  181:     INSERT INTO t4 VALUES(NULL, 3, 4);
  182:   }
  183: } {0 {}}
  184: do_test unique-4.3 {
  185:   execsql {
  186:     SELECT * FROM t4
  187:   }
  188: } {1 2 3 {} 2 {} {} 3 4}
  189: do_test unique-4.4 {
  190:   catchsql {
  191:     INSERT INTO t4 VALUES(2, 2, NULL);
  192:   }
  193: } {0 {}}
  194: do_test unique-4.5 {
  195:   execsql {
  196:     SELECT * FROM t4
  197:   }
  198: } {1 2 3 {} 2 {} {} 3 4 2 2 {}}
  199: 
  200: # Ticket #1301.  Any NULL value in a set of unique columns should
  201: # cause the rows to be distinct.
  202: #
  203: do_test unique-4.6 {
  204:   catchsql {
  205:     INSERT INTO t4 VALUES(NULL, 2, NULL);
  206:   }
  207: } {0 {}}
  208: do_test unique-4.7 {
  209:   execsql {SELECT * FROM t4}
  210: } {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}}
  211: do_test unique-4.8 {
  212:   catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)}
  213: } {0 {}}
  214: do_test unique-4.9 {
  215:   catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)}
  216: } {0 {}}
  217: do_test unique-4.10 {
  218:   catchsql {CREATE UNIQUE INDEX i4c ON t4(b)}
  219: } {1 {indexed columns are not unique}}
  220: integrity_check unique-4.99
  221: 
  222: # Test the error message generation logic.  In particular, make sure we
  223: # do not overflow the static buffer used to generate the error message.
  224: #
  225: do_test unique-5.1 {
  226:   execsql {
  227:     CREATE TABLE t5(
  228:       first_column_with_long_name,
  229:       second_column_with_long_name,
  230:       third_column_with_long_name,
  231:       fourth_column_with_long_name,
  232:       fifth_column_with_long_name,
  233:       sixth_column_with_long_name,
  234:       UNIQUE(
  235:         first_column_with_long_name,
  236:         second_column_with_long_name,
  237:         third_column_with_long_name,
  238:         fourth_column_with_long_name,
  239:         fifth_column_with_long_name,
  240:         sixth_column_with_long_name
  241:       )
  242:     );
  243:     INSERT INTO t5 VALUES(1,2,3,4,5,6);
  244:     SELECT * FROM t5;
  245:   }
  246: } {1 2 3 4 5 6}
  247: do_test unique-5.2 {
  248:   catchsql {
  249:     INSERT INTO t5 VALUES(1,2,3,4,5,6);
  250:   }
  251: } {1 {columns first_column_with_long_name, second_column_with_long_name, third_column_with_long_name, fourth_column_with_long_name, fifth_column_with_long_name, sixth_column_with_long_name are not unique}}
  252: 
  253: finish_test