On some consideration, I don't mind having just a bag rather than an array, so I'll scratch that matter off the list. I can always add an extra key if needed. But thanks for clearing that up. I didn't want to just continue on with that assumption.
The only matter really is size, then. But I guess if that (edit:) doesn't become a problem, or else if I happen to start wanting/needing that extra key anyway (as in, a bag isn't good enough), it'll be a simple matter to add it in. I'll have to remember to examine performance with and without it, too, to see if there's any huge difference in that regard. Thanks for the information On Apr 9, 1:56 pm, Rami Ojares <[email protected]> wrote: > > 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. > > The grammar is very nicely formatted (looks like db2 manuals but even > prettier). > But it is also very compact so you should have a more verbose sql manual for > further clarifications. > > I may be wrong in my syntax since I am currently using derby but this was my > proposed syntax for defining foreign key > > colname coltype REFERENCES referred_table_name (referred_column) > So the constraint is included in the column definition. > > If that is not possible then try > colname coltype, > FOREIGN_KEY(colname) REFERENCES referred_table_name (referred_column) > > Leaving out the referred_column makes the foreign key to point to the primary > key of the referred table. > > > 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? > > Composite primary key means that the combination of the columns is unique and > not null. > This implies that all columns participating in the key are not null. > That's all, I quess. > > > 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? > > Yes I think you are wrong. At least according to the relational model > a table (relation) does not have any kind of ordering between it's rows. > It is a SET of rows. > The only way to order your result set is to use order by clause. > > > 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. > > Well it is quite simple to see how much space one integer column takes. > One row = 4 bytes (+ initial overhead + indexes) > > Only you can tell if that is too much for you. > > - rami > > On 9.4.2010 23:16, Tuxlar wrote: > > > 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.
