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
);
CodePudding user response:
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>