Counterintuitive Table Creation
October 13, 2010 Ted Holt
|
To be able to create one table that is described exactly like an existing table or view is good. But how you create that table determines whether the new table is exactly like the old one or not. Believe it or not, something as trifling as a pair of parentheses can make a difference. Small Things Matter The CREATE TABLE statement has several forms. Here are the two I want to talk about today. CREATE TABLE xxx LIKE zzz CREATE TABLE xxx (LIKE zzz) There’s not much difference–one pair of parentheses. But as amazing as it sounds, the inclusion or omission of parentheses matters. Let me demonstrate. First, create a table. create table someschema/customers (Account dec(7) not null, Name char(20), Class char(1) default 'A', primary key (Account)) Add column headings. label on column someschema/customers (Account is ' Customer Account Number ', Name is ' Customer Name ', Class is ' Customer Class ') Put some data into the table. Notice two inserts. The first one supplies values for all three columns. The second one loads the account and name columns only. insert into someschema/customers values (1001, 'Camus, Albert', 'B'), (1002, 'Maupassant, Guy de', 'A'), (1003, 'Sartre, Jean-Paul', 'B'), (1004, 'Vern, Jules', 'C') insert into someschema/customers (Account, Name) values (1005, 'Gide, Andre') Here’s what the database looks like when I query it with green-screen SQL. select * from customers
Customer
Account Customer Customer
Number Name Class
1,001 Camus, Albert B
1,002 Maupassant, Guy de A
1,003 Sartre, Jean-Paul B
1,004 Vern, Jules C
1,005 Gide, Andre A
Do you see the nice column headings? Do you see that Gide’s classification code defaulted to A? Now create two more tables like the first one. create table someschema/custparen (like someschema/customers) create table someschema/custnoparen like someschema/customers Run the same two inserts against the two new tables. Take a look at the table that was created without parentheses. select * from custnoparen
Customer
Account Customer Customer
Number Name Class
1,001 Camus, Albert B
1,002 Maupassant, Guy de A
1,003 Sartre, Jean-Paul B
1,004 Vern, Jules C
1,005 Gide, Andre A
It looks just like the first table. Now look at the table that was created with parentheses. select * from custparen
ACCOUNT NAME CLASS
1,001 Camus, Albert B
1,002 Maupassant, Guy de A
1,003 Sartre, Jean-Paul B
1,004 Vern, Jules C
1,005 Gide, Andre -
Do you see the differences? The nice column headings are not there, and Gide’s classification code is null (represented by a hyphen.) What happened? According to the IBM Info Center, when you include parentheses around the LIKE clause, the system does not retain certain attributes when making the copy. I copied the following list from the 7.1 Info Center:
Copy Options You may use copy options to further control which attributes are copied. Here’s another CREATE TABLE command. create table someschema/custdft
(like someschema/customers
including column defaults)
After the same two inserts as before, what does this new table look like? ACCOUNT NAME CLASS 1,001 Camus, Albert B 1,002 Maupassant, Guy de A 1,003 Sartre, Jean-Paul B 1,004 Vern, Jules C 1,005 Gide, Andre A Notice that column defaults were copied, but the column headings were not. Here is the list of copy options from the 7.1 documentation. INCLUDING IDENTITY COLUMN ATTRIBUTES EXCLUDING COLUMN DEFAULTS INCLUDING IMPLICITLY HIDDEN COLUMN ATTRIBUTES INCLUDING ROW CHANGE TIMESTAMP COLUMN ATTRIBUTES It Is What It Is This business of including or omitting parentheses is hardly intuitive, but nobody called to ask my opinion when this decision was made. As a rule, I omit the parentheses.
|
