Column restruction

[Gooseman]

Hi,

Is there a way of ensuring that a mySQL table column will contain one
and only one true (with the remainder being false).

I want to set a value of true (or '1') to a row and be assured that
the remaining rows are false (or '0'). Or is this something that has
to be done php side?

Thanks

[Aggro]

Gooseman wrote:

Is there a way of ensuring that a mySQL table column will contain one
and only one true (with the remainder being false).

I want to set a value of true (or '1') to a row and be assured that
the remaining rows are false (or '0'). Or is this something that has
to be done php side?

With two queries that would be simple:

update tablename set value='0';
update tablename set value='1' where id='something';

[steve]

Aggro wrote:

Gooseman wrote:

Is there a way of ensuring that a mySQL table column will contain one and only one true (with the remainder being false).

I want to set a value of true (or ’1’) to a row and be assured that the remaining rows are false (or ’0’). Or is this something that has to be done php side?

With two queries that would be simple:

update tablename set value=’0’;
update tablename set value=’1’ where id=’something’;

Do this if the tables are already populated. Otherwise, if you are
just building the tables, set a default value for the particular
field.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Column...ict124862.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=416074

[Bill Karwin]

steve wrote:

Aggro wrote:

> Gooseman wrote:

> > I want to set a value of true (or ’1’) to a row and be assured
> > that the remaining rows are false (or ’0’). Or is this something
> > that has to be done php side?

> update tablename set value=’0’;
> update tablename set value=’1’ where id=’something’;

Do this if the tables are already populated. Otherwise, if you are
just building the tables, set a default value for the particular
field.

The solutions above _permit_ but do not _enforce_ the condition that
Gooseman described.

We have only a few constraints in MySQL, including primary key, unique
index, and foreign key. MySQL does not seem to support an arbitrary
expression as a table-level constraints as some RDBMS implementations
do. CHECK constraints in CREATE TABLE statements are parsed but not
enforced.

Another way to enforce this is to write triggers on UPDATE and INSERT
operations that validate the value and either abort the operation or
else alter the value that is being input. But according to the web page:
http://dev.mysql.com/doc/mysql/en/AN..._Triggers.html
Triggers are scheduled to be implemented in MySQL 5.1 (and as we all
know, priorities can change).

So I think you're going to have to do the enforcement in your
application code.

Regards,
Bill K.