Creating Oracle Database Table with Numeric Permission Level

user_2186261 0
Viewed 30

I am creating an Oracle database with a table called Roles.
I want the Permission_Level row to store values 1,2,3,4, or 5. How to I limit the input to only those numbers? Here is my attempt:

    CREATE TABLE ROLES 
(
  ROLE_ID NUMBER(20) NOT NULL 
, ROLE_NAME VARCHAR2(20) NOT NULL 
, PERMISSION_LEVEL NUMBER(1,0) NOT NULL 
, CONSTRAINT ROLES_PK PRIMARY KEY 
  (
    ROLE_ID 
  )
  ENABLE 
);
oracle sql
1 Answers

Check constraint is one option:

SQL> create table roles
  2    (role_id         number(20)   constraint roles_pk primary key,
  3     role_name       varchar2(20) not null,
  4     permision_level int          constraint ch_lvl check
  5                                  (permision_level in (1, 2, 3, 4, 5))
  6  );

Table created.

Testing:

SQL> insert into roles (role_id, role_name, permision_level) values (1, 'Role 1', 3);

1 row created.

Level 10 doesn't fit:

SQL> insert into roles (role_id, role_name, permision_level) values (2, 'Role 2', 10);
insert into roles (role_id, role_name, permision_level) values (2, 'Role 2', 10)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_LVL) violated


SQL>
user_90979060
Related