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