mysql help

C

cashmaster

Junior Member

Dec 25, 2010

146

17

• Jan 1, 2012

• #1

While creating a table in a database, is it possible to specify the upper and lower limit for a variable ? For example, if there is a variable called "id" in the table, and i want the value of it between 100 and 1000 only, how is that possible ?

 

W

woofoo

Junior Member

Oct 19, 2011

122

17

• Jan 1, 2012

• #2

make a constraint

Code:

 http://www.w3schools.com/sql/sql_check.asp

 

xpwizard

Junior Member

Nov 6, 2010

198

123

• Jan 1, 2012

• #3

Easier to code the limits via your coding language. That way it's easier to deal with validation/errors.

 

P

p4r4d0x

Newbie

Sep 15, 2010

47

2

• Jan 1, 2012

• #4

it would be best if you check those limits via your code. that way you can have check on strings also using expressions.

let me know if you need any help there.

 

P

pcboy

Registered Member

Feb 20, 2009

60

53

• Jan 1, 2012

• #5

CREATE TABLE tblname
(
Id int NOT NULL,
CONSTRAINT cnst_id CHECK (Id between 100 and 1000)
)

CREATE TABLE tblname
(
Id int NOT NULL,
CONSTRAINT cnst_id CHECK (Id >99 AND ID < 1001)
)

 

Zapdos

Power Member

Oct 22, 2011

597

710

• Jan 1, 2012

• #6

As said before, try to use your actual code to limit rather than the query. In php for example:

Code:

<?php

$max = 100;
$min = 1;
$var = (int) $var;


if ($var > $max) {
  $var = $max;
} elseif ($var < $min) {
  $var = $min;
}

$result = mysql_query("insert into...");

?>

 

Last edited: Jan 1, 2012

A

a_z_0_9

Junior Member

Jul 18, 2011

111

21

• Jan 1, 2012

• #7

why do you want to restrict value between 100 and 1000?

 

C

cashmaster

Junior Member

Dec 25, 2010

146

17

• Jan 2, 2012
• Thread Starter Thread Starter

• #8

Hi

Thanks for all the help. I do know how to code it using php. Its a matter of a simple if-statement. But i didnt remember the CHECK constraint in mysql. Thats what i needed.