For those in who know MySQL

[tomjones2]

hi people

this is the table i have created in mysql 4.1.9 running on php myadmin

create table diss_rec(
diss_num int(6) primary key auto_increment,
hemis_no int(6) not null unique,
dissertation_title char(200) not null,
author_surname char(50) not null,
author_initial char(1)not null,
year_of_sub int(4) not null,
course_code char(10) not null,
grade char(4) not null ,
abstract varchar(3000) not null,
ab_checked boolean not null default "false",
link_checked boolean not null default "false",
FOREIGN KEY (course_code) references course(course_code) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (year_of_sub) references years(year) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (grade) references grade(grade) ON UPDATE RESTRICT ON DELETE RESTRICT
)TYPE = INNODB;

why can i put a enter more than 6 characters into the hemis_no attribute, i though by specifiy the length it stopped this happening

thankyou

[tomjones2]

sorry i meant more than 6 intergers :oops:

i have managed to get 15 in so far

[TimTucker]

I'm no MySQL expert, but I just Googled "MySQL int datatype" and found this:

"Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional display width specification is used to left-pad the display of values having a width less than the width specified for the column."

The width value in parentheses does NOT restrict the actual integer value you're allowed to put into the column. What it means is that the integer value returned will be padded to the required width value with leading spaces if it's shorter than that value.

You have a column defined as INT(6). If you put a value of 1 into that column, this will be returned as " 1" (a 1 with five leading spaces). A value of "1000" will be returned as " 1000" (1000 with a leading space), whilst 1234567 will be returned as "1234567".

I'm not sure if it's possible to restrict the value range within MySQL. I would suggest that the best place to be doing that is in whatever application is collecting the data to be inserted into the table. You shouldn't be relying on your INSERT statement to reject a value because it's too big.

HTH

[tomjones2]

many thanks