==========================================================================
INSERT INTO table1 VALUES (DEFAULT): Auto_increment colums do not count up
==========================================================================
I am trying to insert values via PHP into a table with an autoincrement
field and dynamic column-count. To avoid writing down the column-names of
the target table (insert into table (column1, column2, ...) explicitly, I
was trying to fill up the non-used colums with the value DEFAULT, as it is
suggested in the mySQL documentation.
When inserting using DEFAULT values in SQL, auto_increment fields do not
seem to count up.
As a result, an error "Duplicate entry '182929' for key 1" appears
reproduceably at the second insert statement.
Here are two insert statements while the first works (with empty table) and
the second does not:
delete from import_bericht;
insert into import_bericht values
('02','0066','016','000','000','01610014434',NULL,NULL,NULL,NULL,NULL,'01','
95','00016','016','0000','0000',NULL,'000
000000','00000000',NULL,NULL,'2003-02-21','0536','00000',DEFAULT,DEFAULT,DEF
AULT,DEFAULT);
insert into import_bericht values
('02','0066','016','000','000','01681107025',NULL,NULL,NULL,NULL,NULL,'01','
49','72622','172','0000','0000',NULL,'000
000000','00000000',NULL,NULL,'2003-02-21','0544','00000',DEFAULT,DEFAULT,DEF
AULT,DEFAULT);
I testet autocommit=1 as well as running the statements within a
transaction.
The auto_increment field even remains the same when executing the first
statement twice and a delete in between.
Seems as if I have to workaround that writing down the target colums in SQL.
But would be fine if it worked as described above as it made it easier to
fill tables with dynamic number of colums (just had to read out the number
of colums and fill the rest of the colums with DEFAULT).
Thanks in advance
TM
=================
MySQL 4.0.9 gamma
RedHat Linux 7.3
Dell PowerEdge 2600
2GB RAM
MySQL RPM-Binaries (RedHat) installed
=================
The Table I used:
-- MySQL dump 9.07
--
-- Host: localhost Database: basis
---------------------------------------------------------
-- Server version 4.0.9-gamma-Max
--
-- Table structure for table 'import_bericht'
--
DROP TABLE IF EXISTS import_bericht;
CREATE TABLE import_bericht (
scannart tinyint(3) unsigned NOT NULL default '0',
terminal smallint(3) unsigned NOT NULL default '0',
tour smallint(5) unsigned NOT NULL default '0',
gebiet smallint(3) unsigned default NULL,
stopp_nr smallint(3) unsigned default NULL,
paketnummer bigint(20) unsigned NOT NULL default '0',
fc1 tinyint(3) unsigned default NULL,
fc2 tinyint(3) unsigned default NULL,
fc3 tinyint(3) unsigned default NULL,
fc4 tinyint(3) unsigned default NULL,
fc5 tinyint(3) unsigned default NULL,
paketart tinyint(1) unsigned default NULL,
land tinyint(2) unsigned default NULL,
plz mediumint(5) unsigned default NULL,
route smallint(3) unsigned default NULL,
gk_nr varchar(4) default NULL,
gk_filiale varchar(4) default NULL,
unbenutzt varchar(20) default NULL,
stopp varchar(40) default NULL,
kaufhausnummer int(8) unsigned default NULL,
auftragsnummer varchar(13) default NULL,
importnummer varchar(7) default NULL,
datum date NOT NULL default '0000-00-00',
zeit varchar(4) default NULL,
gewicht mediumint(8) unsigned default NULL,
ber_id int(10) unsigned NOT NULL auto_increment,
send_id int(10) unsigned NOT NULL default '0',
kunden_nr mediumint(8) unsigned default NULL,
importdatum date default NULL,
PRIMARY KEY (ber_id),
KEY idx_pkt (paketnummer),
KEY idx_datum (datum),
KEY idx_kd (kunden_nr),
KEY idx_importdatum (importdatum),
KEY idx_send_id (send_id)
) TYPE=InnoDB;
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
