Edit: Correction, I misinterpreted. Interesting approach. I can't find a mention in the H2 grammar page about using REFERENCES without also specifying FOREIGN KEY, so I'll assume that's what that's doing. I'm a bit unclean on how that composite key works, though. I assume it's another way of saying that a unique primary key exists between _ONLY_ both REF_KEY and INDEX combined?
I know it's a bag if they're unordered, but am I wrong to assume they wouldn't be retrieved in queries in the order they're stored in their physical table? That's enough for me to work with, if so. The only concern I have with using primary keys is if I end up storing a lot of array data, and added keys end up blowing up the physical size of the database more than necessary. 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.
