How to restrict "position" field to int greater than 0?

ppowell

-- LAYOUT POSITIONING PER INDEX INTEGER VALUE
CREATE TABLE /! IF NOT EXISTS / customer_layout_positioning (
customer_id int not null,
layout_id int not null,
additional_layout_id int,
field_name varchar(125) not null,
position int not null,
primary key (customer_id, layout_id, position)
) /! TYPE=MyISAM /;

This is a table that will determine HTML positioning of a reference field column value elsewhere. However, I want the "position" field to be of integer value that is nonzero (even greater than zero). How do I do that?

Thanx
Phil

laserlight

You may be able to declare the column as INT UNSIGNED

ppowell

laserlight wrote:
You may be able to declare the column as INT UNSIGNED

Not sure if I'm reading this article right, but if I assign a field as "int unsigned" does it auto-convert to "bigint"?

http://bugs.mysql.com/bug.php?id=2454

This will be a problem then if I subtract by 1 and 1 - 1 = 0 but 1 - 3 = [some really big int]

Furthermore, I also want to restrict 0 in the "position" field anyway. But hopefully this is a step in the right direction; it at least eliminates all ints < 0.

Phil

devinemke

using any type of unsigned integer column will safeguard againt inserting negative numbers. any negative number entered will be stored as zero. can i assume that you are collecting the data to populate this table via an HTML form? there are various client-side and server-side methods of validating the form data to be a positive (non-zero) numeric value.

ppowell

Yes, but that's not what I would like to do and doesn't ensure a robust database in the process, especially since conventional SQL has the CHECK constraint that MySQL does not. 🙁

I'll be forced to stick with UNSIGNED and hope for the best

Phil

Sxooter

Do you HAVE to use MySQL? Firebird or PostgreSQL both have check constraints (and have had them for years). The words Robust and MySQL don't really go well together. (by robust I mean not just stability, but data integrity)

OTOH, maybe you could implement some sort of pseudo check constraint with MySQL 5.x's fancy new triggers.

csn

Hey Sxooter, do you find it worrisome that Yahoo Finance uses MySQL? ;P

Sxooter

Nah. They don't actually DO finances in it. 🙂 Even Microsoft runs payroll on an AS/400 ya know?

csn

Sxooter wrote:
Even Microsoft runs payroll on an AS/400 ya know?

Yikes, really?