Add Changes entries for 6c77378 and 1fb4f40
[dbsrgits/SQL-Translator.git] / t / data / mysql / entire_syntax.sql
CommitLineData
6661fc69 1-- $Header: /home/faga/work/sqlfairy_svn/sqlfairy-cvsbackup/sqlfairy/t/data/mysql/entire_syntax.sql,v 1.2 2003-08-17 01:41:48 rossta Exp $
dc8bcdc7 2
3-- all data types
4DROP TABLE IF EXISTS t01;
5CREATE TABLE t01 (
6 i01 TINYINT,
7 i02 SMALLINT,
8 i03 MEDIUMINT,
9 i04 INT,
10 i05 INTEGER,
11 i06 BIGINT,
12 r01 REAL,
13 r02 DOUBLE,
6661fc69 14 r03 DOUBLE PRECISION,
15 r04 FLOAT,
dc8bcdc7 16 n01 DECIMAL,
17 n02 NUMERIC,
18 c01 CHAR(10),
19 c02 VARCHAR(10),
20 c03 CHAR(10) BINARY,
21 c04 VARCHAR(10) BINARY,
22-- c05 NATIONAL CHAR(10),
23-- c06 NATIONAL VARCHAR(10),
24-- c07 NATIONAL CHAR(10) BINARY,
25-- c08 NATIONAL VARCHAR(10) BINARY,
26 d01 DATE,
27 d02 TIME,
28 d03 TIMESTAMP,
29 d04 DATETIME,
30 d05 YEAR,
31 b01 TINYBLOB,
32 b02 BLOB,
33 b03 MEDIUMBLOB,
34 b04 LONGBLOB,
35 t01 TINYTEXT,
36 t02 TEXT,
37 t03 MEDIUMTEXT,
38 t04 LONGTEXT,
39
40 e01 ENUM('value1','value2'),
41-- s01 SET('value1','value2'), -- not supported
42
43 i11 TINYINT(1),
44 i12 SMALLINT(1),
45 i13 MEDIUMINT(1),
46 i14 INT(1),
47 i15 INTEGER(1),
48 i16 BIGINT(1),
49 r11 REAL(2,1),
50 r12 DOUBLE(2,1),
6661fc69 51 r13 DOUBLE PRECISION(2,1),
52 r14 FLOAT(1),
53 r15 FLOAT(2,1),
dc8bcdc7 54 n11 DECIMAL(1),
55 n12 DECIMAL(2,1),
56 n13 NUMERIC(2),
57 n14 NUMERIC(2,1),
58 d11 TIMESTAMP(2),
59 d12 YEAR(2)
60);
61
62-- all data types, NOT NULL
63DROP TABLE IF EXISTS t02;
64CREATE TABLE t02 (
65 i01 TINYINT NOT NULL,
66 i02 SMALLINT NOT NULL,
67 i03 MEDIUMINT NOT NULL,
68 i04 INT NOT NULL,
69 i05 INTEGER NOT NULL,
70 i06 BIGINT NOT NULL,
71 r01 REAL NOT NULL,
72 r02 DOUBLE NOT NULL,
6661fc69 73 r03 DOUBLE PRECISION NOT NULL,
74 r04 FLOAT NOT NULL,
dc8bcdc7 75 n01 DECIMAL NOT NULL,
76 n02 NUMERIC NOT NULL,
77 c01 CHAR(10) NOT NULL,
78 c02 VARCHAR(10) NOT NULL,
79 c03 CHAR(10) BINARY NOT NULL,
80 c04 VARCHAR(10) BINARY NOT NULL,
81-- c05 NATIONAL CHAR(10) NOT NULL,
82-- c06 NATIONAL VARCHAR(10) NOT NULL,
83-- c07 NATIONAL CHAR(10) BINARY NOT NULL,
84-- c08 NATIONAL VARCHAR(10) BINARY NOT NULL,
85 d01 DATE NOT NULL,
86 d02 TIME NOT NULL,
87 d03 TIMESTAMP NOT NULL,
88 d04 DATETIME NOT NULL,
89 d05 YEAR NOT NULL,
90 b01 TINYBLOB NOT NULL,
91 b02 BLOB NOT NULL,
92 b03 MEDIUMBLOB NOT NULL,
93 b04 LONGBLOB NOT NULL,
94 t01 TINYTEXT NOT NULL,
95 t02 TEXT NOT NULL,
96 t03 MEDIUMTEXT NOT NULL,
97 t04 LONGTEXT NOT NULL,
98
99 e01 ENUM('value1','value2') NOT NULL,
100-- s01 SET('value1','value2'), -- not supported
101
102 i11 TINYINT(1) NOT NULL,
103 i12 SMALLINT(1) NOT NULL,
104 i13 MEDIUMINT(1) NOT NULL,
105 i14 INT(1) NOT NULL,
106 i15 INTEGER(1) NOT NULL,
107 i16 BIGINT(1) NOT NULL,
108 r11 REAL(2,1) NOT NULL,
109 r12 DOUBLE(2,1) NOT NULL,
6661fc69 110 r13 DOUBLE PRECISION(2,1) NOT NULL,
111 r14 FLOAT(1) NOT NULL,
112 r15 FLOAT(2,1) NOT NULL,
dc8bcdc7 113 n11 DECIMAL(1) NOT NULL,
114 n12 DECIMAL(2,1) NOT NULL,
115 n13 NUMERIC(2) NOT NULL,
116 n14 NUMERIC(2,1) NOT NULL,
117 d11 TIMESTAMP(2) NOT NULL,
118 d12 YEAR(2) NOT NULL
119);
120
121-- all data types, DEFAULT
122DROP TABLE IF EXISTS t03;
123CREATE TABLE t03 (
124 i01 TINYINT DEFAULT 0,
125 i02 SMALLINT DEFAULT 0,
126 i03 MEDIUMINT DEFAULT 0,
127 i04 INT DEFAULT 0,
128 i05 INTEGER DEFAULT 0,
129 i06 BIGINT DEFAULT 0,
130 r01 REAL DEFAULT 0,
131 r02 DOUBLE DEFAULT 0,
6661fc69 132 r03 DOUBLE PRECISION DEFAULT 0,
133 r04 FLOAT DEFAULT 0,
dc8bcdc7 134 n01 DECIMAL DEFAULT 0,
135 n02 NUMERIC DEFAULT 0,
136 c01 CHAR(10) DEFAULT '',
137 c02 VARCHAR(10) DEFAULT '',
138 c03 CHAR(10) BINARY DEFAULT '',
139 c04 VARCHAR(10) BINARY DEFAULT '',
140-- c05 NATIONAL CHAR(10) DEFAULT '',
141-- c06 NATIONAL VARCHAR(10) DEFAULT '',
142-- c07 NATIONAL CHAR(10) BINARY DEFAULT '',
143-- c08 NATIONAL VARCHAR(10) BINARY DEFAULT '',
144 d01 DATE DEFAULT 0,
145 d02 TIME DEFAULT 0,
146 d03 TIMESTAMP DEFAULT 0,
147 d04 DATETIME DEFAULT 0,
148 d05 YEAR DEFAULT 0,
149 b01 TINYBLOB DEFAULT '',
150 b02 BLOB DEFAULT '',
151 b03 MEDIUMBLOB DEFAULT '',
152 b04 LONGBLOB DEFAULT '',
153 t01 TINYTEXT DEFAULT '',
154 t02 TEXT DEFAULT '',
155 t03 MEDIUMTEXT DEFAULT '',
156 t04 LONGTEXT DEFAULT '',
157
158 e01 ENUM('value1','value2') DEFAULT 'value1',
159-- s01 SET('value1','value2'), -- not supported
160
161 i11 TINYINT(1) DEFAULT 0,
162 i12 SMALLINT(1) DEFAULT 0,
163 i13 MEDIUMINT(1) DEFAULT 0,
164 i14 INT(1) DEFAULT 0,
165 i15 INTEGER(1) DEFAULT 0,
166 i16 BIGINT(1) DEFAULT 0,
167 r11 REAL(2,1) DEFAULT 0,
168 r12 DOUBLE(2,1) DEFAULT 0,
6661fc69 169 r13 DOUBLE PRECISION(2,1) DEFAULT 0,
170 r14 FLOAT(1) DEFAULT 0,
171 r15 FLOAT(2,1) DEFAULT 0,
dc8bcdc7 172 n11 DECIMAL(1) DEFAULT 0,
173 n12 DECIMAL(2,1) DEFAULT 0,
174 n13 NUMERIC(2) DEFAULT 0,
175 n14 NUMERIC(2,1) DEFAULT 0,
176 d11 TIMESTAMP(2) DEFAULT 0,
177 d12 YEAR(2) DEFAULT 0
178);
179
180-- all data types, NOT NULL DEFAULT
181DROP TABLE IF EXISTS t04;
182CREATE TABLE t04 (
183 i01 TINYINT NOT NULL DEFAULT 0,
184 i02 SMALLINT NOT NULL DEFAULT 0,
185 i03 MEDIUMINT NOT NULL DEFAULT 0,
186 i04 INT NOT NULL DEFAULT 0,
187 i05 INTEGER NOT NULL DEFAULT 0,
188 i06 BIGINT NOT NULL DEFAULT 0,
189 r01 REAL NOT NULL DEFAULT 0,
190 r02 DOUBLE NOT NULL DEFAULT 0,
6661fc69 191 r03 DOUBLE PRECISION NOT NULL DEFAULT 0,
192 r04 FLOAT NOT NULL DEFAULT 0,
dc8bcdc7 193 n01 DECIMAL NOT NULL DEFAULT 0,
194 n02 NUMERIC NOT NULL DEFAULT 0,
195 c01 CHAR(10) NOT NULL DEFAULT '',
196 c02 VARCHAR(10) NOT NULL DEFAULT '',
197 c03 CHAR(10) BINARY NOT NULL DEFAULT '',
198 c04 VARCHAR(10) BINARY NOT NULL DEFAULT '',
199-- c05 NATIONAL CHAR(10) NOT NULL DEFAULT '',
200-- c06 NATIONAL VARCHAR(10) NOT NULL DEFAULT '',
201-- c07 NATIONAL CHAR(10) BINARY NOT NULL DEFAULT '',
202-- c08 NATIONAL VARCHAR(10) BINARY NOT NULL DEFAULT '',
203 d01 DATE NOT NULL DEFAULT 0,
204 d02 TIME NOT NULL DEFAULT 0,
205 d03 TIMESTAMP NOT NULL DEFAULT 0,
206 d04 DATETIME NOT NULL DEFAULT 0,
207 d05 YEAR NOT NULL DEFAULT 0,
208 b01 TINYBLOB NOT NULL DEFAULT '',
209 b02 BLOB NOT NULL DEFAULT '',
210 b03 MEDIUMBLOB NOT NULL DEFAULT '',
211 b04 LONGBLOB NOT NULL DEFAULT '',
212 t01 TINYTEXT NOT NULL DEFAULT '',
213 t02 TEXT NOT NULL DEFAULT '',
214 t03 MEDIUMTEXT NOT NULL DEFAULT '',
215 t04 LONGTEXT NOT NULL DEFAULT '',
216
217 e01 ENUM('value1','value2') NOT NULL DEFAULT 'value1',
218-- s01 SET('value1','value2'), -- not supported
219
220 i11 TINYINT(1) NOT NULL DEFAULT 0,
221 i12 SMALLINT(1) NOT NULL DEFAULT 0,
222 i13 MEDIUMINT(1) NOT NULL DEFAULT 0,
223 i14 INT(1) NOT NULL DEFAULT 0,
224 i15 INTEGER(1) NOT NULL DEFAULT 0,
225 i16 BIGINT(1) NOT NULL DEFAULT 0,
226 r11 REAL(2,1) NOT NULL DEFAULT 0,
227 r12 DOUBLE(2,1) NOT NULL DEFAULT 0,
6661fc69 228 r13 DOUBLE PRECISION(2,1) NOT NULL DEFAULT 0,
229 r14 FLOAT(1) NOT NULL DEFAULT 0,
230 r15 FLOAT(2,1) NOT NULL DEFAULT 0,
dc8bcdc7 231 n11 DECIMAL(1) NOT NULL DEFAULT 0,
232 n12 DECIMAL(2,1) NOT NULL DEFAULT 0,
233 n13 NUMERIC(2) NOT NULL DEFAULT 0,
234 n14 NUMERIC(2,1) NOT NULL DEFAULT 0,
235 d11 TIMESTAMP(2) NOT NULL DEFAULT 0,
236 d12 YEAR(2) NOT NULL DEFAULT 0
237);
238
239-- /*
240-- /*-- all data types, NULL
241-- /*DROP TABLE IF EXISTS t05;
242-- /*CREATE TABLE t05 (
243-- /* i01 TINYINT NULL,
244-- /* i02 SMALLINT NULL,
245-- /* i03 MEDIUMINT NULL,
246-- /* i04 INT NULL,
247-- /* i05 INTEGER NULL,
248-- /* i06 BIGINT NULL,
249-- /* r01 REAL NULL,
250-- /* r02 DOUBLE NULL,
6661fc69 251-- /* r03 DOUBLE PRECISION NULL,
252-- /* r04 FLOAT NULL,
dc8bcdc7 253-- /* n01 DECIMAL NULL,
254-- /* n02 NUMERIC NULL,
255-- /* c01 CHAR(10) NULL,
256-- /* c02 VARCHAR(10) NULL,
257-- /* c03 CHAR(10) BINARY NULL,
258-- /* c04 VARCHAR(10) BINARY NULL,
259-- /*-- c05 NATIONAL CHAR(10) NULL,
260-- /*-- c06 NATIONAL VARCHAR(10) NULL,
261-- /*-- c07 NATIONAL CHAR(10) BINARY NULL,
262-- /*-- c08 NATIONAL VARCHAR(10) BINARY NULL,
263-- /* d01 DATE NULL,
264-- /* d02 TIME NULL,
265-- /* d03 TIMESTAMP NULL,
266-- /* d04 DATETIME NULL,
267-- /* d05 YEAR NULL,
268-- /* b01 TINYBLOB NULL,
269-- /* b02 BLOB NULL,
270-- /* b03 MEDIUMBLOB NULL,
271-- /* b04 LONGBLOB NULL,
272-- /* t01 TINYTEXT NULL,
273-- /* t02 TEXT NULL,
274-- /* t03 MEDIUMTEXT NULL,
275-- /* t04 LONGTEXT NULL,
276-- /*
277-- /* e01 ENUM('value1','value2') NULL,
278-- /*-- s01 SET('value1','value2'), -- supported
279-- /*
280-- /* i11 TINYINT(1) NULL,
281-- /* i12 SMALLINT(1) NULL,
282-- /* i13 MEDIUMINT(1) NULL,
283-- /* i14 INT(1) NULL,
284-- /* i15 INTEGER(1) NULL,
285-- /* i16 BIGINT(1) NULL,
286-- /* r11 REAL(2,1) NULL,
287-- /* r12 DOUBLE(2,1) NULL,
6661fc69 288-- /* r13 DOUBLE PRECISION(2,1) NULL,
289-- /* r14 FLOAT(1) NULL,
290-- /* r15 FLOAT(2,1) NULL,
dc8bcdc7 291-- /* n11 DECIMAL(1) NULL,
292-- /* n12 DECIMAL(2,1) NULL,
293-- /* n13 NUMERIC(2) NULL,
294-- /* n14 NUMERIC(2,1) NULL,
295-- /* d11 TIMESTAMP(2) NULL,
296-- /* d12 YEAR(2) NULL
297-- /*);
298-- /*
299-- /*-- all data types, NULL DEFAULT
300-- /*DROP TABLE IF EXISTS t06;
301-- /*CREATE TABLE t06 (
302-- /* i01 TINYINT NULL DEFAULT 0,
303-- /* i02 SMALLINT NULL DEFAULT 0,
304-- /* i03 MEDIUMINT NULL DEFAULT 0,
305-- /* i04 INT NULL DEFAULT 0,
306-- /* i05 INTEGER NULL DEFAULT 0,
307-- /* i06 BIGINT NULL DEFAULT 0,
308-- /* r01 REAL NULL DEFAULT 0,
309-- /* r02 DOUBLE NULL DEFAULT 0,
6661fc69 310-- /* r03 DOUBLE PRECISION NULL DEFAULT 0,
311-- /* r04 FLOAT NULL DEFAULT 0,
dc8bcdc7 312-- /* n01 DECIMAL NULL DEFAULT 0,
313-- /* n02 NUMERIC NULL DEFAULT 0,
314-- /* c01 CHAR(10) NULL DEFAULT '',
315-- /* c02 VARCHAR(10) NULL DEFAULT '',
316-- /* c03 CHAR(10) BINARY NULL DEFAULT '',
317-- /* c04 VARCHAR(10) BINARY NULL DEFAULT '',
318-- /*-- c05 NATIONAL CHAR(10) NULL DEFAULT '',
319-- /*-- c06 NATIONAL VARCHAR(10) NULL DEFAULT '',
320-- /*-- c07 NATIONAL CHAR(10) BINARY NULL DEFAULT '',
321-- /*-- c08 NATIONAL VARCHAR(10) BINARY NULL DEFAULT '',
322-- /* d01 DATE NULL DEFAULT 0,
323-- /* d02 TIME NULL DEFAULT 0,
324-- /* d03 TIMESTAMP NULL DEFAULT 0,
325-- /* d04 DATETIME NULL DEFAULT 0,
326-- /* d05 YEAR NULL DEFAULT 0,
327-- /* b01 TINYBLOB NULL DEFAULT '',
328-- /* b02 BLOB NULL DEFAULT '',
329-- /* b03 MEDIUMBLOB NULL DEFAULT '',
330-- /* b04 LONGBLOB NULL DEFAULT '',
331-- /* t01 TINYTEXT NULL DEFAULT '',
332-- /* t02 TEXT NULL DEFAULT '',
333-- /* t03 MEDIUMTEXT NULL DEFAULT '',
334-- /* t04 LONGTEXT NULL DEFAULT '',
335-- /*
336-- /* e01 ENUM('value1','value2') NULL DEFAULT 'value1',
337-- /*-- s01 SET('value1','value2'), -- supported
338-- /*
339-- /* i11 TINYINT(1) NULL DEFAULT 0,
340-- /* i12 SMALLINT(1) NULL DEFAULT 0,
341-- /* i13 MEDIUMINT(1) NULL DEFAULT 0,
342-- /* i14 INT(1) NULL DEFAULT 0,
343-- /* i15 INTEGER(1) NULL DEFAULT 0,
344-- /* i16 BIGINT(1) NULL DEFAULT 0,
345-- /* r11 REAL(2,1) NULL DEFAULT 0,
346-- /* r12 DOUBLE(2,1) NULL DEFAULT 0,
6661fc69 347-- /* r13 DOUBLE PRECISION(2,1) NULL DEFAULT 0,
348-- /* r14 FLOAT(1) NULL DEFAULT 0,
349-- /* r15 FLOAT(2,1) NULL DEFAULT 0,
dc8bcdc7 350-- /* n11 DECIMAL(1) NULL DEFAULT 0,
351-- /* n12 DECIMAL(2,1) NULL DEFAULT 0,
352-- /* n13 NUMERIC(2) NULL DEFAULT 0,
353-- /* n14 NUMERIC(2,1) NULL DEFAULT 0,
354-- /* d11 TIMESTAMP(2) NULL DEFAULT 0,
355-- /* d12 YEAR(2) NULL DEFAULT 0
356-- /*);
357-- /**/
358
359-- all data types, UNSIGNED
360DROP TABLE IF EXISTS t11;
361CREATE TABLE t11 (
362 i01 TINYINT UNSIGNED NOT NULL DEFAULT 0,
363 i02 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
364 i03 MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
365 i04 INT UNSIGNED NOT NULL DEFAULT 0,
366 i05 INTEGER UNSIGNED NOT NULL DEFAULT 0,
367 i06 BIGINT UNSIGNED NOT NULL DEFAULT 0,
368 r01 REAL UNSIGNED NOT NULL DEFAULT 0,
369 r02 DOUBLE UNSIGNED NOT NULL DEFAULT 0,
6661fc69 370 r03 DOUBLE PRECISION UNSIGNED NOT NULL DEFAULT 0,
371 r04 FLOAT UNSIGNED NOT NULL DEFAULT 0,
dc8bcdc7 372 n01 DECIMAL UNSIGNED NOT NULL DEFAULT 0,
373 n02 NUMERIC UNSIGNED NOT NULL DEFAULT 0,
374
375 i11 TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
376 i12 SMALLINT(1) UNSIGNED NOT NULL DEFAULT 0,
377 i13 MEDIUMINT(1) UNSIGNED NOT NULL DEFAULT 0,
378 i14 INT(1) UNSIGNED NOT NULL DEFAULT 0,
379 i15 INTEGER(1) UNSIGNED NOT NULL DEFAULT 0,
380 i16 BIGINT(1) UNSIGNED NOT NULL DEFAULT 0,
381 r11 REAL(2,1) UNSIGNED NOT NULL DEFAULT 0,
382 r12 DOUBLE(2,1) UNSIGNED NOT NULL DEFAULT 0,
6661fc69 383 r13 DOUBLE PRECISION(2,1) UNSIGNED NOT NULL DEFAULT 0,
384 r14 FLOAT(1) UNSIGNED NOT NULL DEFAULT 0,
385 r15 FLOAT(2,1) UNSIGNED NOT NULL DEFAULT 0,
dc8bcdc7 386 n11 DECIMAL(1) UNSIGNED NOT NULL DEFAULT 0,
387 n12 DECIMAL(2,1) UNSIGNED NOT NULL DEFAULT 0,
388 n13 NUMERIC(2) UNSIGNED NOT NULL DEFAULT 0,
389 n14 NUMERIC(2,1) UNSIGNED NOT NULL DEFAULT 0
390);
391
392-- all data types, ZEROFILL
393DROP TABLE IF EXISTS t12;
394CREATE TABLE t12 (
395 i01 TINYINT ZEROFILL NOT NULL DEFAULT 0,
396 i02 SMALLINT ZEROFILL NOT NULL DEFAULT 0,
397 i03 MEDIUMINT ZEROFILL NOT NULL DEFAULT 0,
398 i04 INT ZEROFILL NOT NULL DEFAULT 0,
399 i05 INTEGER ZEROFILL NOT NULL DEFAULT 0,
400 i06 BIGINT ZEROFILL NOT NULL DEFAULT 0,
401 r01 REAL ZEROFILL NOT NULL DEFAULT 0,
402 r02 DOUBLE ZEROFILL NOT NULL DEFAULT 0,
6661fc69 403 r03 DOUBLE PRECISION ZEROFILL NOT NULL DEFAULT 0,
404 r04 FLOAT ZEROFILL NOT NULL DEFAULT 0,
dc8bcdc7 405 n01 DECIMAL ZEROFILL NOT NULL DEFAULT 0,
406 n02 NUMERIC ZEROFILL NOT NULL DEFAULT 0,
407
408 i11 TINYINT(1) ZEROFILL NOT NULL DEFAULT 0,
409 i12 SMALLINT(1) ZEROFILL NOT NULL DEFAULT 0,
410 i13 MEDIUMINT(1) ZEROFILL NOT NULL DEFAULT 0,
411 i14 INT(1) ZEROFILL NOT NULL DEFAULT 0,
412 i15 INTEGER(1) ZEROFILL NOT NULL DEFAULT 0,
413 i16 BIGINT(1) ZEROFILL NOT NULL DEFAULT 0,
414 r11 REAL(2,1) ZEROFILL NOT NULL DEFAULT 0,
415 r12 DOUBLE(2,1) ZEROFILL NOT NULL DEFAULT 0,
6661fc69 416 r13 DOUBLE PRECISION(2,1) ZEROFILL NOT NULL DEFAULT 0,
417 r14 FLOAT(1) ZEROFILL NOT NULL DEFAULT 0,
418 r15 FLOAT(2,1) ZEROFILL NOT NULL DEFAULT 0,
dc8bcdc7 419 n11 DECIMAL(1) ZEROFILL NOT NULL DEFAULT 0,
420 n12 DECIMAL(2,1) ZEROFILL NOT NULL DEFAULT 0,
421 n13 NUMERIC(2,1) ZEROFILL NOT NULL DEFAULT 0,
422 n14 NUMERIC(2,1) ZEROFILL NOT NULL DEFAULT 0
423);
424
425-- all data types, UNSIGNED ZEROFILL
426DROP TABLE IF EXISTS t13;
427CREATE TABLE t13 (
428 i01 TINYINT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
429 i02 SMALLINT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
430 i03 MEDIUMINT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
431 i04 INT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
432 i05 INTEGER UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
433 i06 BIGINT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
434 r01 REAL UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
435 r02 DOUBLE UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
6661fc69 436 r03 DOUBLE PRECISION UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
437 r04 FLOAT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
dc8bcdc7 438 n01 DECIMAL UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
439 n02 NUMERIC UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
440
441 i11 TINYINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
442 i12 SMALLINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
443 i13 MEDIUMINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
444 i14 INT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
445 i15 INTEGER(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
446 i16 BIGINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
447 r11 REAL(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
448 r12 DOUBLE(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
6661fc69 449 r13 DOUBLE PRECISION(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
450 r14 FLOAT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
451 r15 FLOAT(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
dc8bcdc7 452 n11 DECIMAL(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
6661fc69 453 n12 DECIMAL(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
dc8bcdc7 454 n13 NUMERIC(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
6661fc69 455 n14 NUMERIC(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0
dc8bcdc7 456);
457
458-- all keys
459DROP TABLE IF EXISTS t21;
460CREATE TABLE t21 (
461 c01 CHAR(10) NOT NULL,
462 c02 VARCHAR(10) NOT NULL,
463
464 KEY k01 (c01),
465 KEY k02 (c01(1)),
466 INDEX k03 (c01),
467 INDEX k04 (c01(1)),
468 UNIQUE k05 (c01),
469 UNIQUE k06 (c01(1)),
470 UNIQUE INDEX k07 (c01),
471 UNIQUE INDEX k08 (c01(1)),
472 FULLTEXT k09 (c01),
473 FULLTEXT INDEX k10 (c01),
474
475 KEY k11 (c01, c02),
476 KEY k12 (c01(1), c02),
477 INDEX k13 (c01, c02),
478 INDEX k14 (c01(1), c02),
479 UNIQUE k15 (c01, c02),
480 UNIQUE k16 (c01(1), c02),
481 UNIQUE INDEX k17 (c01, c02),
482 UNIQUE INDEX k18 (c01(1), c02),
483 FULLTEXT k19 (c01, c02),
484 FULLTEXT INDEX k20 (c01, c02),
485
486 PRIMARY KEY (c01)
487);
488
489-- multi-field primary key
490DROP TABLE IF EXISTS t22;
491CREATE TABLE t22 (
492 c01 VARCHAR(10) NOT NULL,
493 c02 VARCHAR(10) NOT NULL,
494 PRIMARY KEY (c01, c02)
495);
496
497-- multi-field primary key with length
498DROP TABLE IF EXISTS t23;
499CREATE TABLE t23 (
500 c01 VARCHAR(255) NOT NULL,
501 c02 VARCHAR(255) NOT NULL,
502 PRIMARY KEY (c01(10), c02(10))
503);
504
505-- primary key 2nd syntax
506DROP TABLE IF EXISTS t24;
507CREATE TABLE t24 (
508 c01 VARCHAR(255) NOT NULL PRIMARY KEY,
509 c02 VARCHAR(255) NOT NULL
510);
511
512-- all data types, AUTO_INCREMENT
513DROP TABLE IF EXISTS k01;
514CREATE TABLE k01 (
515 i01 TINYINT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
516);
517
518DROP TABLE IF EXISTS k02;
519CREATE TABLE k02 (
520 i02 SMALLINT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
521);
522
523DROP TABLE IF EXISTS k03;
524CREATE TABLE k03 (
525 i03 MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
526);
527
528DROP TABLE IF EXISTS k04;
529CREATE TABLE k04 (
530 i04 INT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
531);
532
533DROP TABLE IF EXISTS k05;
534CREATE TABLE k05 (
535 i05 INTEGER UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
536);
537
538DROP TABLE IF EXISTS k06;
539CREATE TABLE k06 (
540 i06 BIGINT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
541);
542
543DROP TABLE IF EXISTS k11;
544CREATE TABLE k11 (
545 i11 TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
546);
547
548DROP TABLE IF EXISTS k12;
549CREATE TABLE k12 (
550 i12 SMALLINT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
551);
552
553DROP TABLE IF EXISTS k13;
554CREATE TABLE k13 (
555 i13 MEDIUMINT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
556);
557
558DROP TABLE IF EXISTS k14;
559CREATE TABLE k14 (
560 i14 INT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
561);
562
563DROP TABLE IF EXISTS k15;
564CREATE TABLE k15 (
565 i15 INTEGER(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
566);
567
568DROP TABLE IF EXISTS k16;
569CREATE TABLE k16 (
570 i16 BIGINT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
571);