List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 21 2006 7:50pm
Subject:Re: confused...
View as plain text  
Patrick Duda <pduda@stripped> 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


Thread
confused...Patrick Duda21 Feb
  • Re: confused...Hugh Sasse21 Feb
  • Re: confused...SGreen21 Feb
  • Re: confused...Peter Brawley21 Feb