Akka Jaworek Akka Jaworek - 4 months ago 15
SQL Question

how to store object with enum field type in oracle database

I have following class in my program, i want this class to be stored in oracle 11c database, i know how to store most of the info but how to store usertype which is of type enum? i have following scheme for data model:

here should be picture

and this is mu user class

public class User {

@Id
protected Identity<User> id;

protected String identifier;

protected UserType type;

protected String password;

public static enum UserType {
CUSTOMER, COURIER;
}

}

Answer

With only two fixed values, the simplest way is with a check constraint:

create table users (
  id number primary key,
  identifier varchar2(30),
  password raw(64),
  type varchar2(8) not null check (type in ('CUSTOMER', 'COURIER'))
);

The check (type in ('CUSTOMER', 'COURIER')) is pretty self-explanatory; Oracle checks that the value supplied for that column is one of those two values. Assuming you want it always be set, the not null ensures that it can't be left empty.

insert into users (id, identifier, password, type)
values (1, 'Bob', null, 'CUSTOMER');

1 row inserted.

insert into users (id, identifier, password, type)
values (2, 'Alice', null, null);

ORA-01400: cannot insert NULL into ("SCHEMA"."USERS"."TYPE")

insert into users (id, identifier, password, type)
values (2, 'Alice', null, 'INVALID');

ORA-02290: check constraint (SCHEMA.SYS_C00113048) violated

It would be a bit better to use named constraints:

create table users (
  id number,
  identifier varchar2(30),
  password raw(64),
  type varchar2(8) not null,
  constraint users_pk primary key (id),
  constraint users_type_ck check (type in ('CUSTOMER', 'COURIER'))
);

More generally, particularly with values that can change, you would have a look-up table and a foreign key relationship:

create table user_types (
  id number,
  description varchar2(8),
  constraint user_types_pk primary key (id),
  constraint user_types_desc_uk unique (description)
);

create table users (
  id number,
  identifier varchar2(30),
  password raw(64),
  type_id number not null,
  constraint users_pk primary key (id),
  constraint users_type_fk foreign key (type_id) references user_types(id)
);

insert into user_types (id, description) values (1, 'CUSTOMER');
insert into user_types (id, description) values (2, 'COURIER');

insert into users (id, identifier, password, type_id)
values (1, 'Bob', null, 1);

1 row inserted.

insert into users (id, identifier, password, type_id)
values (2, 'Alice', null, null);

ORA-01400: cannot insert NULL into ("SCHEMA"."USERS"."TYPE_ID")

insert into users (id, identifier, password, type_id)
values (2, 'Alice', null, 3);

ORA-02291: integrity constraint (SCHEMA.USERS_TYPE_FK) violated - parent key not found