[thelist] A SQL request on a SET() field type

[Seb]

Damien Cola asked:
> Hello,
> the = operator doesn't work for checking if a field has the value:
>
> Select * from mytable where mysetfield = 'UK';
> works if mysetfield is ('UK')
> doesn't work if mysetfield is for example: ('UK', 'FR')
>
> I have tried replacing = with LIKE with no success.
>
> What is the secret of the SET type ?
>
> I've looked up the documentation (online and books) but with no mention of
> my particular case.

Hi Damien,

IIRC, you need something along the lines of:

SELECT * FROM mytable WHERE 'UK' IN mysetfield;

Which should be work fine, because the DB treats the value of a set type as
an array, not a single value.

It's been a long time since I went near set or enum types, as they only
cause pain.

These types are a compound data structure (in this case, a simple array)
being held within a single field. This breaks the requirements of the "first
normal form" in an RDBMS data model, which requires (amongst other things)
that any datum held within your DB field is atomic, ie: it cannot be broken
down into smaller self-contained units.

The reason behind this is that if you wanted to change the value 'UK' to
'GB' because your client needs it that way, you have a real data integrity
issue. If, however, your structure is more like this:

Table_1:
ID
OTHER_FIELDS
Type_ID

Table_2:
Type_ID
Type_Name

Then you can restructure your query as:

SELECT Table_1.ID, Table_1.OTHER_FIELDS, Table_2.Type_Name FROM Table_1,
Table_2 WHERE ( Table_2.Type_ID = Table_1.Type_ID AND
Table_2.Type_Name='UK' );

If your Type_ID in Table_1 is defined as a foreign key, then this kind of
select is lightning fast.

Of course, you may have better reasons to use a set type, but I thought this
to be worth pointing out. If you've got any questions on DB design, please
feel free to ask away.


Cheers,

Seb.
http://www.sebpotter.org