我的程序中有以下类,我想将此类存储在oracle 11c数据库中,我知道如何存储大多数信息,但如何存储枚举类型的usertype?我对数据模型有以下方案:
这是亩用户类
public class User {
@Id
protected Identity<User> id;
protected String identifier;
protected UserType type;
protected String password;
public static enum UserType {
CUSTOMER, COURIER;
}
}
只有两个固定值,最简单的方法是带有检查约束:
create table users (
id number primary key,
identifier varchar2(30),
password raw(64),
type varchar2(8) not null check (type in ('CUSTOMER', 'COURIER'))
);
这check (type in ('CUSTOMER', 'COURIER'))
是不言自明的;Oracle检查为该列提供的值是这两个值之一。假设您希望始终对其进行设置,请not null
确保不能将其保留为空。
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
使用命名约束会更好一些:
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'))
);
更一般地,特别是对于可以更改的值,您将具有一个查询表和一个外键关系:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句