----- Original Message -----
From: "Harald Fuchs" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 29, 2004 7:37 AM
Subject: Re: Fw: column choices for certain data
> In article <[EMAIL PROTECTED]>,
> "Rhino" <[EMAIL PROTECTED]> writes:
>
> >> The chief advantage of 'SET', as far as I can tell from the manual, is
> > that
> >> it lets you control the specific values which can be in a column
without
> >> having to write application lookups to verify that the value you are
> >> supplying is one that is valid for the 'SET' column. Therefore, if you
had
> >> only 3 business types, sole proprietorship, partnership, and
corporation,
> >> you could put those 3 values in the set and be sure that those are the
> > only
> >> 3 values that would ever be allowed in the column. That's fine as far
as
> > it
> >> goes and is a very useful thing.
>
> It would be mildly useful if it were true. Unfortunately, it isn't.
> When you try to insert invalid values, MySQL doesn't complain.
> Instead, it silently does a conversion to something equally invalid:
>
> CREATE TABLE t1 (
> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
> val SET ('foo', 'bar', 'baz') NOT NULL,
> PRIMARY KEY (id)
> );
>
> INSERT INTO t1 (val) VALUES ('foo');
> INSERT INTO t1 (val) VALUES ('foo,bar');
> INSERT INTO t1 (val) VALUES ('qux');
> INSERT INTO t1 (val) VALUES ('foo,qux');
>
> SELECT id, val FROM t1;
>
> returns
> +----+---------+
> | id | val |
> +----+---------+
> | 1 | foo |
> | 2 | foo,bar |
> | 3 | |
> | 4 | foo |
> +----+---------+
>
Damn! I only skimmed the article and then did a simple experiment with 'SET'
and got a rather incorrect impression of what it is doing.
I just did this experiment, which was more in-depth, due to your remarks and
learned a few things about 'SET' which are described below:
--------------------------------------------
select 'Drop/Create table';
drop table if exists myset;
create table if not exists myset
(id smallint not null,
name char(10) not null,
sex set('M','F') not null,
primary key(id));
select 'Populate table';
insert into myset values(1, 'Smith', 'M');
insert into myset values(2, 'Jones', 'F');
insert into myset values(3, 'Green', 'M,F');
insert into myset values(4, 'Brown', null);
insert into myset values(5, 'White', 'G');
insert into myset values(6, 'Black', 'M,G');
select 'Display table contents';
select * from myset;
--------------------------------------------
When I wrote this script, I assumed that I was limiting the values in the
'sex' column to 'M' and 'F' (and 'not null' ensured that one or the other of
these values would be stored so that the column wasn't left to default to
null).
When I ran this script, I got this result:
--------------------------------------------
+-------------------+
| Drop/Create table |
+-------------------+
| Drop/Create table |
+-------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+----------------+
| Populate table |
+----------------+
| Populate table |
+----------------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
ERROR 1048: Column 'sex' cannot be null
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+------------------------+
| Display table contents |
+------------------------+
| Display table contents |
+------------------------+
1 row in set (0.00 sec)
+----+-------+-----+
| id | name | sex |
+----+-------+-----+
| 1 | Smith | M |
| 2 | Jones | F |
| 3 | Green | M,F |
| 5 | White | |
| 6 | Black | M |
+----+-------+-----+
5 rows in set (0.00 sec)
--------------------------------------------
In other words, the first three rows, which stored 'M', 'F', and 'M,F'
respectively in the 'sex' column worked fine. The fourth row, which tried to
store null in the 'sex' column, failed because the column is defined 'not
null'. The fifth row, which tried to store 'G' in the 'sex' column failed
silently and a blank was stored instead of the invalid value 'G'. The sixth
row, which tried to store the a combination of a valid value, 'M' and an
invalid value 'G', failed silently and actually stored 'M' and a blank.
According to the manual "if you set a SET column to an unsupported value,
the value will be ignored". In other words, MySQL doesn't even think it is
an error to try to store something that isn't in the defined values for the
set ('M','F') in this case, so that is why you don't get an error message.
Therefore, it is clear that the SET column type is NOT working in the way I
first thought: it does not give you an error if you try to store a value
that is not in its defined list of values, although it won't store invalid
values in the table. As a result, I still feel vindicated in suggesting that
the business types be stored in their own table, which could be joined back
to the original Businesses table any time that someone needs to know which
business types a given business has. The lookup table will also work the way
I said.
However, I may have been hasty in dismissing the use of the SET column type
altogether. In rereading the article on SET, I came across a reference to
another article about 'SET' in the user notes. The article, written by Mike
Hillyer, has been incorporated into the manual at this URL:
<http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html>.
Frankly, after reading this article, I doubt I would ever have a use for SET
in my own work but I guess I can see that SET might be useful in a few
specific situations like the ones he describes. By the way, Hillyer
acknowledges that this column type violates Codd's rules of normalization
which is one of my biggest reasons for avoiding this column type. However, I
suppose "rules are made to be broken" so, if someone thinks the benefits of
the SET column type outweigh the costs, it is there for them to use.
I guess the biggest lesson here is that I shouldn't have jumped into this
discussion with such a skimpy knowledge of the SET column type. My apologies
for doing that!
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
