I am assuming you have two columns like
REF_KEY = a foreign key pointing to the table that has (contains) the array.
VALUE = the value that the array holds.
If I am right then where do you have the array index?
If you have no index then your collection is not an array but a bag (or
set if you make a constraint that each value is unique within the
collection which is already a bit difficult).
So if you want an array add a new column index.
This you would have to constrain to be unique within the array.
This can be achieved defining a composite key over REF_KEY and INDEX and
making it unique.
And this key then can be your PRIMARY_KEY.
I would strongly suggest always defining some sort of primary for a table.
This way you can always differentiate one row from another.
CREATE TABLE MY_ARRAY (
REF_KEY INT REFERENCES TBL_WHO_CONTAINS_THIS_ARRAY,
INDEX INT CHECK INDEX > 0,
VALUE [here comes the type of your array],
PRIMARY KEY(REF_KEY, INDEX)
);
Primary key should make the combination of REF_KEY and INDEX unique and
not null.
What do you think?
- rami
On 9.4.2010 14:40, Tuxlar wrote:
I've decided for a few reasons I don't want to use the ARRAY data type
to store arbitrary groups of foreign keys, so I'm instead using a
separate, two column table. Two columns only, though, because I don't
think I ought to need a primary key for it. Am I wrong?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
