Patrick Duda <[EMAIL PROTECTED]> wrote on 02/21/2006 02:39:47 PM:
> Why, when I create a table as follows:
>
> mysql> create table requestid ( request_id int not null default
> 1, constraint requestid_innodb_pk_cons primary key(request_id) )
> ENGINE=InnoDB;
> Query OK, 0 rows affected (0.02 sec)
>
>
> Do I get the following?
>
> mysql> select request_id from requestid;
> Empty set (0.01 sec)
>
> When I do a show create table I see:
>
> mysql> show create table requestid;
> +-----------
>
+----------------------------------------------------------------------------------------------------------------------------------------------
> +
> | Table | Create
> Table
> |
> +-----------
>
+----------------------------------------------------------------------------------------------------------------------------------------------
> +
> | requestid | CREATE TABLE `requestid` (
> `request_id` int(11) NOT NULL default '1',
> PRIMARY KEY (`request_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
> +-----------
>
+----------------------------------------------------------------------------------------------------------------------------------------------
> +
> 1 row in set (0.00 sec)
>
> Shouldn't I be getting back a '1' when I do my select??? Why am I
getting
> an empty set? What am I not understanding? How do I create a table
with a
> starting value of '1' or '0' for an int???
>
> Thanks
>
You haven't created any rows yet. That's why you get nothing back from
your SELECT query. With a single-column table like this, it will be
impossible to add a row to the table without providing a value for ID
(because it's the only column). You will never see the default value
because you must always supply one.
The term "starting value" in your original post implies that you intended
some sort of sequence. Did you want the server to automatically increment
the request_id value for you each time you add a record to this table? If
so, you have to do two things:
1) add more columns to this table
2) change the definition of your ID column to be an auto_increment column.
Here is an example of what your `request` table may look like
CREATE TABLE `request` (
id int not null auto_increment,
details varchar(50) not null,
tsModified timestamp,
PRIMARY KEY(id)
);
and you could add reqests to it like this:
INSERT `request`(`details`) VALUES ('details of your first
request'),('details of a second request'), ('details of a third request');
Is it making any better sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
