I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows and they
both seem to look fine {see the SHOW CREATE TABLE's following the CREATE
TABLE statements}
RUN ON 4.0.20
mysql> CREATE TABLE ID (
-> mat INT UNIQUE PRIMARY KEY,
-> ID_firstname CHAR(35) DEFAULT 'filler',
-> ID_lastname CHAR(35) DEFAULT 'filler',
-> ID_ramqnb CHAR(12) DEFAULT 'filler',
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
-> ID_streettype_hosp CHAR(6) DEFAULT '-9',
-> ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9',
-> ID_numciv_study CHAR(10) DEFAULT '-9'
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> show create table ID;
+-------+---------------------------------------------------------------
-------------------------
------------------------------------------------------------------------
-------------------------
| Table | Create Table
+-------+---------------------------------------------------------------
-------------------------
------------------------------------------------------------------------
-------------------------
| ID | CREATE TABLE `ID` (
`mat` int(11) NOT NULL default '0',
`ID_firstname` char(35) default 'filler',
`ID_lastname` char(35) default 'filler',
`ID_ramqnb` char(12) default 'filler',
`ID_numciv_hosp` char(10) default '-9',
`ID_appt_hosp` char(10) default '-9',
`ID_streetname_hosp` char(75) default '-9',
`ID_streettype_hosp` char(6) default '-9',
`ID_streettype_spec_hosp` char(25) default 'humbug',
`ID_direction_hosp` char(2) default '-9',
`ID_city_hosp` char(50) default '-9',
`ID_city_spec_hosp` char(150) default 'filler',
`ID_province_hosp` char(2) default 'QC',
`ID_postal_code_hosp` char(7) default '-9',
`ID_phone_number_hosp` char(12) default '-9',
`ID_work_number_hosp` char(20) default '-9',
`ID_cell_number_hosp` char(12) default '-9',
`ID_numciv_study` char(10) default '-9',
PRIMARY KEY (`mat`),
UNIQUE KEY `mat` (`mat`)
) TYPE=MyISAM |
+-------+---------------------------------------------------------------
-------------------------
------------------------------------------------------------------------
-------------------------
1 row in set (0.00 sec)
________________________________________________________________________
________________________________________________________________________
_______
RUN ON 5.0.6
mysql> CREATE TABLE ID (
-> mat INT UNIQUE PRIMARY KEY,
-> ID_firstname CHAR(35) DEFAULT 'filler',
-> ID_lastname CHAR(35) DEFAULT 'filler',
-> ID_ramqnb CHAR(12) DEFAULT 'filler',
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
-> ID_streettype_hosp CHAR(6) DEFAULT '-9',
-> ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9',
-> ID_numciv_study CHAR(10) DEFAULT '-9'
-> );
Query OK, 0 rows affected (0.91 sec)
mysql>
mysql> CREATE TABLE ID1 (
-> mat INT PRIMARY KEY UNIQUE,
-> ID_firstname CHAR(35),
-> ID_lastname CHAR(35),
-> ID_ramqnb CHAR(12),
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
-> ID_streettype_hosp CHAR(6) DEFAULT '-9',
-> ID_streettype_spec_hosp CHAR(25),
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150),
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9'
->
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> show create table ID;
+-------+---------------------------------------------------------------
-------------------------------------
------------------------------------------------------------------------
-------------------------------------
| Table | Create Table
+-------+---------------------------------------------------------------
-------------------------------------
------------------------------------------------------------------------
-------------------------------------
| ID | CREATE TABLE `id` (
`mat` int(11) NOT NULL,
`ID_firstname` char(35) default 'filler',
`ID_lastname` char(35) default 'filler',
`ID_ramqnb` char(12) default 'filler',
`ID_numciv_hosp` char(10) default '-9',
`ID_appt_hosp` char(10) default '-9',
`ID_streetname_hosp` char(75) default '-9',
`ID_streettype_hosp` char(6) default '-9',
`ID_streettype_spec_hosp` char(25) default 'humbug',
`ID_direction_hosp` char(2) default '-9',
`ID_city_hosp` char(50) default '-9',
`ID_city_spec_hosp` char(150) default 'filler',
`ID_province_hosp` char(2) default 'QC',
`ID_postal_code_hosp` char(7) default '-9',
`ID_phone_number_hosp` char(12) default '-9',
`ID_work_number_hosp` char(20) default '-9',
`ID_cell_number_hosp` char(12) default '-9',
`ID_numciv_study` char(10) default '-9',
PRIMARY KEY (`mat`),
UNIQUE KEY `mat` (`mat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------
-------------------------------------
------------------------------------------------------------------------
-------------------------------------
1 row in set (0.05 sec)
mysql> show create table ID1;
+-------+---------------------------------------------------------------
-------------------------------------
------------------------------------------------------------------------
-------------------------------------
| Table | Create Table
+-------+---------------------------------------------------------------
-------------------------------------
------------------------------------------------------------------------
-------------------------------------
| ID1 | CREATE TABLE `id1` (
`mat` int(11) NOT NULL,
`ID_firstname` char(35) default NULL,
`ID_lastname` char(35) default NULL,
`ID_ramqnb` char(12) default NULL,
`ID_numciv_hosp` char(10) default '-9',
`ID_appt_hosp` char(10) default '-9',
`ID_streetname_hosp` char(75) default '-9',
`ID_streettype_hosp` char(6) default '-9',
`ID_streettype_spec_hosp` char(25) default NULL,
`ID_direction_hosp` char(2) default '-9',
`ID_city_hosp` char(50) default '-9',
`ID_city_spec_hosp` char(150) default NULL,
`ID_province_hosp` char(2) default 'QC',
`ID_postal_code_hosp` char(7) default '-9',
`ID_phone_number_hosp` char(12) default '-9',
`ID_work_number_hosp` char(20) default '-9',
`ID_cell_number_hosp` char(12) default '-9',
PRIMARY KEY (`mat`),
UNIQUE KEY `mat` (`mat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------
-------------------------------------
------------------------------------------------------------------------
-------------------------------------
1 row in set (0.00 sec)
-----Original Message-----
From: Les Schaffer [mailto:[EMAIL PROTECTED]
Sent: Monday, June 13, 2005 2:27 PM
To: mysql@lists.mysql.com
Subject: CREATE TABLE and specifying DEFAULT
i am trying to create a table as follows:
CREATE TABLE ID (
mat INT PRIMARY KEY UNIQUE,
ID_firstname CHAR(35),
ID_lastname CHAR(35),
ID_ramqnb CHAR(12),
ID_numciv_hosp CHAR(10) DEFAULT '-9',
ID_appt_hosp CHAR(10) DEFAULT '-9',
ID_streetname_hosp CHAR(75) DEFAULT '-9',
ID_streettype_hosp CHAR(6) DEFAULT '-9',
ID_streettype_spec_hosp CHAR(25),
ID_direction_hosp CHAR(2) DEFAULT '-9',
ID_city_hosp CHAR(50) DEFAULT '-9',
ID_city_spec_hosp CHAR(150),
ID_province_hosp CHAR(2) DEFAULT 'QC',
ID_postal_code_hosp CHAR(7) DEFAULT '-9',
ID_phone_number_hosp CHAR(12) DEFAULT '-9',
ID_work_number_hosp CHAR(20) DEFAULT '-9',
ID_cell_number_hosp CHAR(12) DEFAULT '-9'
... lots and lots more columns
);
the table is created almost correctly, excepts there are no DEFAULTs
showing up after ID_streettype_hosp. the remaining columns all show NULL
for default.
the result is the same if i truncate the list at the last one shown.
but even this fails:
CREATE TABLE ID (
mat INT UNIQUE PRIMARY KEY,
ID_firstname CHAR(35) DEFAULT 'filler',
ID_lastname CHAR(35) DEFAULT 'filler',
ID_ramqnb CHAR(12) DEFAULT 'filler',
ID_numciv_hosp CHAR(10) DEFAULT '-9',
ID_appt_hosp CHAR(10) DEFAULT '-9',
ID_streetname_hosp CHAR(75) DEFAULT '-9',
ID_streettype_hosp CHAR(6) DEFAULT '-9',
ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
ID_direction_hosp CHAR(2) DEFAULT '-9',
ID_city_hosp CHAR(50) DEFAULT '-9',
ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
ID_province_hosp CHAR(2) DEFAULT 'QC',
ID_postal_code_hosp CHAR(7) DEFAULT '-9',
ID_phone_number_hosp CHAR(12) DEFAULT '-9',
ID_work_number_hosp CHAR(20) DEFAULT '-9',
ID_cell_number_hosp CHAR(12) DEFAULT '-9',
ID_numciv_study CHAR(10) DEFAULT '-9'
);
in that the DEFAULT is lost after ID_streettype_hosp.
am i doing something stupid or is there a limit to how many columns one
can include in a CREATE TABLE statement???
mysql> describe id;
+-------------------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+-----------+------+-----+---------+-------+
| mat | int(11) | NO | PRI | | |
| ID_firstname | char(35) | YES | | filler | |
| ID_lastname | char(35) | YES | | filler | |
| ID_ramqnb | char(12) | YES | | filler | |
| ID_numciv_hosp | char(10) | YES | | -9 | |
| ID_appt_hosp | char(10) | YES | | -9 | |
| ID_streetname_hosp | char(75) | YES | | -9 | |
| ID_streettype_hosp | char(6) | YES | | -9 | |
| ID_streettype_spec_hosp | char(25) | YES | | NULL | |
| ID_direction_hosp | char(2) | YES | | NULL | |
| ID_city_hosp | char(50) | YES | | NULL | |
| ID_city_spec_hosp | char(150) | YES | | NULL | |
| ID_province_hosp | char(2) | YES | | NULL | |
| ID_postal_code_hosp | char(7) | YES | | NULL | |
| ID_phone_number_hosp | char(12) | YES | | NULL | |
| ID_work_number_hosp | char(20) | YES | | NULL | |
| ID_cell_number_hosp | char(12) | YES | | NULL | |
| ID_numciv_study | char(10) | YES | | NULL | |
+-------------------------+-----------+------+-----+---------+-------+
18 rows in set (0.01 sec)
thnx...
les schaffer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
