having problems with auto_increment and the numeric data type

[jfunchio]

I'm using mysql and phpmyadmin to create a video rental database and I've been having problems with the auto_increment feature and the numeric data type. I have auto_increment on multiple tables for their table id's. The problem is when i enter data into the tables the counter doesnt reset. So if I enter 4 actors their id's would be 1,2,3,4 but then when i enter in data for the customers the count starts where the last table left off so if i enter 4 customers their id's come up as 5,6,7,8 when they should be 1,2,3,4.

My problem with the numeric data type is when ever i enter anything into that field it always shows up as .99 unless i enter something lower than .99 then it works fine but if i try to enter 3 or 3.00 it come up as .99. I think i might not have declared it right by I'm not sure. I've also tried decimal and it did the same thing.

These are my tables.

create table actor
(  actor_id             int not null auto_increment,
   first_name           varchar(20),
   last_name            varchar(20),
   primary key (actor_id)
) ENGINE = InnoDB;

create table customer
(  customer_id          int not null auto_increment,
   first_name           varchar(20),
   last_name            varchar(20),
   phone_number         varchar(20),
   email                varchar(50),
   primary key(customer_id)
) ENGINE = InnoDB;

create table movie
(  movie_id             varchar(10),
   title                varchar(50),
   release_year         numeric(4,0),
   rental_duration      tinyint,
   rental_rate          numeric(2,2),
   rating               varchar(5),
   genre                varchar(20),
   stock                int,
   primary key (movie_id)
) ENGINE = InnoDB;

create table movie_actor
(  actor_id             int,
   movie_id             varchar(10),
   foreign key (actor_id) references actor (actor_id),
   foreign key (movie_id) references movie (movie_id)
) ENGINE = InnoDB;

create table rental
(  rental_id            int not null auto_increment,
   rental_date          date,
   customer_id          int,
   return_date          date,
   primary key (rental_id),
   foreign key (customer_id)  references customer (customer_id)
) ENGINE = InnoDB;

create table payment
(  payment_id           int not null auto_increment,
   customer_id          int,
   rental_id            int,
   amount               numeric(2,2),
   payment_date         date,
   primary key (payment_id),
   foreign key (customer_id) references customer (customer_id),
   foreign key (rental_id) references rental (rental_id)
) ENGINE = InnoDB;

This is the file I used to enter the starting data into the tables

delete from actor;
delete from customer;
delete from movie;
delete from movie_actor;
delete from payment;
delete from rental;
insert into actor values ('null', 'Bradley', 'Cooper');
insert into actor values ('null', 'Ed', 'Helms');
insert into actor values ('null', 'Zach', 'Galifianakis');
insert into actor values ('null', 'Leonardo', 'DiCaprio');
insert into actor values ('null', 'Ben', 'Kingsley');
insert into actor values ('null', 'Mark', 'Ruffalo');
insert into actor values ('null', 'Denzel', 'Washington');
insert into actor values ('null', 'Will', 'Patton');
insert into customer values ('null', 'Bob', 'Smith', '330-678-2732', '[e-mail]');
insert into customer values ('null', 'Steve', 'Johnsan', '330-238-2342', '[e-mail]');
insert into customer values ('null', 'Amy', 'Davis', '330-283-8392', '[e-mail]');
insert into customer values ('null', 'Elizabeth', 'Veres', '330-687-8941', '[e-mail]');
insert into customer values ('null', 'Jacob', 'Funchion', '419-966-6617', '[e-mail]');
insert into movie values ('mov-00001', 'The Hangover', '2009', '5', '3.00', 'R', 'Comedy', '5');
insert into movie values ('mov-00006', 'Beauty and the Beast', '1991', '5', '300', 'G', 'Family', '5');

[hielo]

executing DELETE FROM `customer`; will remove all the records from the table, but it will NOT reset the auto-increment counter. Imagine that you start with customer=(1, 'Bob', 'Smith', '330-678-2732', '[e-mail]') (where 1 is the customer_id ) and this customer exists in the rental table. If you delete all the records from customer, you will still have customer_id=1 in the [B]`rental`[/B] table.

If the db were to automatically "reset" the ids and you then insert data into the customer table - ex: (NULL, 'Sally', 'Jones', '...', '...') , then due to the automatic resetting that you are expecting, then Sally would now be customer_id=1 .

Clearly this would be a problem since the original renter was Bob, but based on your new record it would appear that Sally was that one that rented the movie. To avoid this, the auto_increment does NOT reset by default when you remove all the records. If you want to reset it you will need to do so explicitly: ALTER TABLE `customer` AUTO_INCREMENT = 1; On another note, technically speaking this is not correct:
INSERT INTO actor VALUES ('null', 'Bradley', 'Cooper');

the proper way to insert a NULL is to NOT quote it. In that line you are attempting to insert a STRING that consists of four characters which spell out the word null. The reason it works is because your field is auto_increment and it will ignore any non-numeric value you attempt to insert since the value it will ultimately use it the next in the sequence. So it should actually be: INSERT INTO actor VALUES (NULL, 'Bradley', 'Cooper'); Also, if you have a field declared as a NUMERIC datatype, don't put apostrophes around the value you are inserting. Use apostrophes around non-numeric values only -ex. INSERT INTO movie VALUES ('mov-00001', 'The Hangover', 2009, 5, 3.00, 'R', 'Comedy',5); Having said that, this: rental_rate NUMERIC(2, 2), basically says "rental_rate should consist of a total of 2 digits out of which 2 of them should be to the right of the decimal point. The left argument should be the sum of the total number of digits you want (left + right side of decimal point) and the second argument should be the number of digits that make up the fractional part - ex: 1200.945: NUMERIC(7,3)