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 2021 - 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
----------------------------------------------------------------------