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.
