In your parlance I was talking about Table 2.
Here is the terminology mapping
You Me
--- --
ARRAY_KEY REF_KEY
ARRAY_ITEM VALUE
And I feel my analysis still holds (although you can model data many ways)
I think H2 will be just fine without primary keys.
It's you, the user, that I am more worried about ;-)
And if you are worried about performance (like people usually are) then I
think that has something to do with the uniqueness of the attribute,
it's datatype and indexes built on it.
PRIMARY KEY means a unique not null column that has been set aside as
the primary way of identifying the row in a table.
- rami
On 9.4.2010 22:54, Tuxlar wrote:
Actually I meant it more like this:
Table 1:
ID = Primary key for this table.
ARRAY_KEY = A key corresponding to all the items in another table that
are part of this row's array.
...and other columns
Table 2:
ARRAY_KEY = The key that maps each array item in this table to the
array they belong to.
ARRAY_ITEM = The array item.
This is how I plan to normalize my arrays. Basically I want to know
if this is a good idea or not, and also if H2 is fine without having
unique primary keys for table 2 items (as I know some databases have
issues with tables that don't have a primary key...).
Did that explain it?
On Apr 9, 5:45 am, Rami Ojares<[email protected]> wrote:
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.
