Entry
Database: Relational: Key: Primary: Unique: How to increment the AUTO_INCREMENT field? [NULL]
May 22nd, 2005 00:45
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 21 May 2005 - 04:44 pm ------------------------
Database: Relational: Key: Primary: Unique: How to increment the
AUTO_INCREMENT field? [NULL]
===
You could use to
1. not including that auto_increment field name, when doing an insert.
2. Or you could use to insert a NULL value in that autoincrement field.
---
Method: When doing an INSERT, do not include the AUTO_INCREMENT
columnname
---
Steps: Overview:
1. -Create a table with an AUTO_INCREMENT field,
and some other field(s)
--- cut here: begin --------------------------------------------------
-----------------------------------------------
CREATE TABLE
table1
(
columnPrimaryKeyI INT AUTO_INCREMENT PRIMARY KEY,
columnFirstNameS VARCHAR( 50 ),
columnLastNameS VARCHAR( 100 )
)
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
2. -Insert a value in the other fields
(but thus not in the AUTO_INCREMENT field,
but in one or more of the other fields)
--- cut here: begin --------------------------------------------------
-----------------------------------------------
INSERT INTO
table1
(
columnFirstNameS,
columnLastNameS
)
VALUES
(
'Vanessa',
'Bella'
)
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
3. -You will see that MySql has automatically
increased the counter with 1, at each of
that inserts
--- cut here: begin --------------------------------------------------
-----------------------------------------------
SELECT
*
FROM
table1
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
1. -This will show
--- cut here: begin --------------------------------------------------
+-------------------+------------------+-----------------+
| columnPrimaryKeyI | columnFirstNameS | columnLastNameS |
+-------------------+------------------+-----------------+
| 1 | Vanessa | Bella |
+-------------------+------------------+-----------------+
1 row in set (0.03 sec)
--- cut here: end ----------------------------------------------------
---
4. -Inserting again a record, you will see that MySql has
automatically increased the counter with 1
(so showing a total of 2)
--- cut here: begin --------------------------------------------------
-----------------------------------------------
INSERT INTO
table1
(
columnFirstNameS,
columnLastNameS
)
VALUES
(
'Vanessa',
'Bella'
)
;
-----------------------------------------------
SELECT
*
FROM
table1
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
1. -This will show
--- cut here: begin --------------------------------------------------
+-------------------+------------------+-----------------+
| columnPrimaryKeyI | columnFirstNameS | columnLastNameS |
+-------------------+------------------+-----------------+
| 1 | Vanessa | Bella |
| 2 | Vanessa | Bella |
+-------------------+------------------+-----------------+
2 rows in set (0.01 sec)
--- cut here: end ----------------------------------------------------
===
2. Method: When doing an INSERT, insert a NULL value in the
AUTO_INCREMENT column
---
1. -Create a table with an AUTO_INCREMENT field,
and some other field(s).
---
Note: 'NOT NULL'
If you tell when creating this table that that the
AUTO_INCREMENT field should be 'NOT NULL', then this method
will by design not work.
--- cut here: begin --------------------------------------------------
-----------------------------------------------
CREATE TABLE
table1
(
columnPrimaryKeyI INT AUTO_INCREMENT PRIMARY KEY,
columnFirstNameS VARCHAR( 50 ),
columnLastNameS VARCHAR( 100 )
)
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
2. -Insert a value a NULL value in the AUTO_INCREMENT
field
(and possibly (also) some value(s) in the other
fields, though that is not of influence on the
autoincrement)
--- cut here: begin --------------------------------------------------
-----------------------------------------------
INSERT INTO
table1
(
columnPrimaryKeyI,
columnFirstNameS,
columnLastNameS
)
VALUES
(
NULL,
'Vanessa',
'Bella'
)
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
3. -You will see that MySql has automatically
increased the counter with 1
--- cut here: begin --------------------------------------------------
-----------------------------------------------
SELECT
*
FROM
table1
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
1. -This will show
--- cut here: begin --------------------------------------------------
+-------------------+------------------+-----------------+
| columnPrimaryKeyI | columnFirstNameS | columnLastNameS |
+-------------------+------------------+-----------------+
| 1 | Vanessa | Bella |
+-------------------+------------------+-----------------+
1 row in set (0.00 sec)
--- cut here: end ----------------------------------------------------
4. -Increasing again the auto_increment field value, by
inserting a NULL value
--- cut here: begin --------------------------------------------------
-----------------------------------------------
INSERT INTO
table1
(
columnPrimaryKeyI,
columnFirstNameS,
columnLastNameS
)
VALUES
(
NULL,
'Vanessa',
'Bella'
)
;
-----------------------------------------------
SELECT
*
FROM
table1
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
1. -This will show
--- cut here: begin --------------------------------------------------
+-------------------+------------------+-----------------+
| columnPrimaryKeyI | columnFirstNameS | columnLastNameS |
+-------------------+------------------+-----------------+
| 1 | Vanessa | Bella |
| 2 | Vanessa | Bella |
+-------------------+------------------+-----------------+
2 rows in set (0.00 sec)
--- cut here: end ----------------------------------------------------
---
---
Book: see also:
---
[book: author: Welling, Luke / Thomson, Laura - title: PHP and MySQL
web development - publisher: SAMS - year: 2001 - ISBN 0-672-31784-2]
---
---
Internet: see also:
---
Database: Relational: MySql: Auto_Increment: Link: Overview: Can you
give an overview of links?
http://www.faqts.com/knowledge_base/view.phtml/aid/36428/fid/70
---
Database: Language: SQL: Overview: Can you give an overview of links
about SQL?
http://www.faqts.com/knowledge_base/view.phtml/aid/32811/fid/54
----------------------------------------------------------------------
