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.
- Rami
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.
