2013/6/17 Rami Ojares <[email protected]>: > You can also model an enum with relational concepts. > Let me show you. > > CREATE TABLE PERIOD ( > PERIOD_ID IDENTITY PRIMARY KEY, > PERIOD_NAME VARCAHR(10) UNIQUE, > MAYBE_SOME_OTHER_PERIOD_RELATED_ATTRIBUTE VARCHAR > ); > Now insert the possible periods as tuples into the relation period. > INSERT INTO PERIOD VALUES (DEFAULT, 'daily', '...'); > ... > Then define a constraint that prohibits inserts and updates to table PERIOD > This can be done utilizing H2's triggers. > Then when you want to use the period enum as type of an attribute define it > like this > > MY_ATTRIBUTE BIGINT REFERENCES PERIOD ON UPDATE CASCADE > > Now you can relate data to enum values and all changes are propagated > automatically throughout the database.
I had thought about this also, but thought the other variant was better. (Do not ask me why.) What are the pro's and cons of the two solutions? Why are you using PERIOD_ID and do not put the primary key on period_name? By the way, at the moment it is not an issue, but I think in the future it could. What if I would like to use different periods in different tables. For example in one table I can use daily, weekly, monthly and another table I can use weekly, monthly, yearly. I suppose that could be best done with another table and a field with flags. > On 17.6.2013 0:52, Cecil Westerhof wrote: >> >> H2 does not have enums. I ‘solved’ this in the following way: >> DROP TABLE IF EXISTS typeGroups; >> CREATE TABLE typeGroups ( >> typeGroupID UUID PRIMARY KEY, >> description VARCHAR(50) UNIQUE NOT NULL >> ); >> >> DROP TABLE IF EXISTS types; >> DROP DOMAIN IF EXISTS period; >> CREATE DOMAIN period AS >> VARCHAR(10) DEFAULT 'daily' >> CHECK VALUE IN ( >> 'daily', >> 'weekly', >> 'monthly', >> 'yearly' >> ); >> CREATE TABLE types ( >> typeID UUID PRIMARY KEY, >> description VARCHAR(50) UNIQUE NOT NULL, >> typeGroupID UUID NOT NULL, >> ranking SMALLINT, >> evalPeriod period, >> >> FOREIGN KEY (typeGroupID) REFERENCES typeGroups(typeGroupID) >> ); >> >> Is this correct, or is there a better way? >> > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > > -- Cecil Westerhof -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
