1
-----------------------
2
KEXI ALTER TABLE ISSUES
3
-----------------------
6
- different databases have different set of altering capabilities
7
(SQLite has no table altering capabilities at all; re-creation is needed:
8
this has high cost in time and disk space for large tables)
9
- we need to collect every detailed change made in Table Designer and apply
10
these changes to existing table schema
11
- in the case when a table is already filled with data, we need to perform
12
tasks to move that data to a new structure,
13
especially if database engine can't do it for us or if the engine
14
wants simply drop the data because convertion cannot be done automatically
15
- because of above issues, some work needs to be done at the client side
17
2. What can be altered using ALTER TABLE:
19
(into a specified position)
20
ADD COLUMN <column_definition> [FIRST | AFTER <col_name> ]
22
(adding many colums at the end)
23
ADD COLUMN <column_definition>,...
25
2.2 Adding constraints, indices
26
ADD INDEX [<index_name>] [<index_type>] (<index_col_name>,...)
28
ADD CONSTRAINT [<symbol>] PRIMARY KEY [<index_type>] (<index_col_name>,...)
30
ADD CONSTRAINT [<symbol>] UNIQUE [<index_name>] [<index_type>] (<index_col_name>,...)
32
ADD CONSTRAINT [<symbol>] FOREIGN KEY [<index_name>] (<index_col_name>,...)
33
[<reference_definition>]
35
2.3 Altering column properties
36
ALTER COLUMN <col_name> {SET DEFAULT <literal> | DROP DEFAULT}
38
CHANGE COLUMN <old_col_name> column_definition [FIRST|AFTER <col_name>]
41
CHANGE <old_column> <new_column>
43
MODIFY COLUMN <column_definition> [FIRST | AFTER <col_name>]
46
DROP COLUMN <col_name>
50
DROP INDEX <index_name>
52
DROP FOREIGN KEY <fk_symbol>
55
RENAME TO <new_tbl_name>
58
3. How to perform table altering be re-creation:
59
SQLite: (taken from Ticket 236: Add RENAME TABLE
60
- will be easier to work around missing ALTER TABLE
61
http://www.sqlite.org/cvstrac/tktview?tn=236,8)
62
Currently the recommended method to ALTER TABLE is:
64
1. Create a temporary table.
65
2. Copy all data from original table to temporary table.
66
3. Drop the original table.
67
4. Create a new table.
68
5. Copy all data from the temporary table to the new table.
70
For example, suppose you have a table named "t1" with columns
71
names "a", "b", and "c" and that you want to delete column "c"
72
from this table. The following steps illustrate how this could be done:
75
CREATE TEMPORARY TABLE t1_backup(a,b);
76
INSERT INTO t1_backup SELECT a,b FROM t1;
79
INSERT INTO t1 SELECT a,b FROM t1_backup;
83
If the table name also changes on altering, it's easier to do the
84
altering (no temp. table needed):
88
INSERT INTO t2 (c,d) SELECT a,b FROM t1;
92
If we had RENAME TABLE, we'd get easier alter table here:
93
1. Rename original table to temp name.
94
2. Create new table with ORIGINAL name.
95
3. Move data from previous to new table.
96
4. Drop previous table.
102
5. Useful documentation
103
http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
109
Asked Richard Hipp about RENAME TABLE