MySql script does not set default to 0

  • VentsyV
  • Student
  • Student
  • VentsyV
  • Posts: 65

Post 3+ Months Ago

Hi all. i am trying to write a small database driven web site. It needs to have a simple script for adding news and loading them dynamically. That means the newest first. I am designing database and a php script to quentry the database. I am quite confitable with PHP (I know C++) but the SQL is kind of confusing.
here is my SQL:
Code: [ Select ]
create database rok;
use rok
create table users
(
  user_id  int(2)      default '0' not null auto_increment,
  user_name varchar(25)  not null,
  user_pass varchar(12)  not null,
  primary  key(user_id),
  key (user_name),
);

create table news
(
  news_id int(3) default 0 not null auto_increment,
  news_title       varchar(50) not null,
  news_date timestamp(6) not null,
  news_text varchar(15),
  news_by  int(2),
  Primary Key (news_id),
);
  1. create database rok;
  2. use rok
  3. create table users
  4. (
  5.   user_id  int(2)      default '0' not null auto_increment,
  6.   user_name varchar(25)  not null,
  7.   user_pass varchar(12)  not null,
  8.   primary  key(user_id),
  9.   key (user_name),
  10. );
  11. create table news
  12. (
  13.   news_id int(3) default 0 not null auto_increment,
  14.   news_title       varchar(50) not null,
  15.   news_date timestamp(6) not null,
  16.   news_text varchar(15),
  17.   news_by  int(2),
  18.   Primary Key (news_id),
  19. );

It executes alright, but the news_id and user_id are not set default 0. I looked it up in winmysqladmin. The code seems right. Any ideas ? Any tools that might be usefull ?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

Because auto_increment modifier set's that column value to the next id base upon the values already inserted. Usually, you will insert new data without sending a value for ID in order to make it an auto incremented value.
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

In a table with 34 as the highest value for ID column, a new insert will use 35 as id.

Note, that is not true always. If you deleted a previous record with 35 as id, the new id will be 36 even when 35 were not there.
  • VentsyV
  • Student
  • Student
  • VentsyV
  • Posts: 65

Post 3+ Months Ago

I will not send value when adding data. The problem is that the field is not marked as default 0. When I insert the first record what id will it get ? What if I want to start from 5 ?
How did you came up with 34 as top value ? 3 bytes are 2^16-1 top value. Is it bytes ?
  • alpha5th
  • Newbie
  • Newbie
  • alpha5th
  • Posts: 8
  • Loc: Denmark

Post 3+ Months Ago

auto_increment columns start at 1. They cannot have a default value.

I you want to start with a different # than 1. After the table is created, use:

Code: [ Select ]
alter table users auto_increment = 5;



You can find more information in the manual under;
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
http://dev.mysql.com/doc/mysql/en/examp ... EMENT.html
  • VentsyV
  • Student
  • Student
  • VentsyV
  • Posts: 65

Post 3+ Months Ago

great thanks a lot. You will hear from me some more very soon ;)

Post Information

  • Total Posts in this topic: 6 posts
  • Users browsing this forum: No registered users and 91 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.