Omer Zak wrote:


Daniel Feiglin wrote:

Hello folks!

I have a problem with SQL for MySQL (and pre4sumably any other kind):

Can anone offer a RTFM do do something like this:

create table foo (
    boo    int,
    bar varchar;
    baz array[0..9] of int, # illegal, but needed!
    ...
);

I'm not really thrilled about stuffing all the data into a blob or a varchar and using c/c++/whatever text handling.


Some questions:
1. Do you always have exactly 10 integers in baz?
2. Does their order in baz matter?
3. Is a value in baz constrained to appear exactly once?

At any case:

Why not normalize the table into two tables (I am making here the
guess that boo is the only key):

   create table foo_bar (
        boo    int,    # This is the key
        bar varchar);

   create table foo_baz (
        boo    int,    # This is the key
        baz int);

Under Postgres (other databases that are not MySQL have similar constructs), this should really be:
create table foo_bar (
boo serial unique, -- This is the key
bar varchar );


create table foo_baz (
   boo integer references foo_bar(boo),
   baz int );

This makes foo_baz.boo a foreign key poining at foo_bar.boo. This automatically means:
- You can't create an entry in foo_baz that references a non-existing boo entry in foo_bar.
- You can't delete an entry from foo_bar if it has entries referencing it in foo_baz
If you want to list all the entries, just do:
select foo_bar.boo, bar, baz from foo_bar left join foo_baz on foo_bar.boo=foo_baz.boo;


Of course, if you want the database to enforce stuff for you, you can add that. For example, if it's not allowed to have two values in the "array" the same, you add a constraint on foo_baz that says that the pair boo, baz has to be unique. Likewise, if you want ordering, and want to enforce no more than ten, say that foo_baz has a field "index" of type int, and put a constraint in that says that boo, index must be unique, and that index is only allowed the range 1-10 (0-9).

            Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/


================================================================= To unsubscribe, send mail to [EMAIL PROTECTED] with the word "unsubscribe" in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]



Reply via email to