Omer Zak wrote:
Under Postgres (other databases that are not MySQL have similar constructs), this should really be:
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);
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]
