In SQL101 series, I will post some simple SQL related techniques. Most of them are posted in OTN time to time by me.
This SQL101 is related to check constraint. We can implement check constraint very efficiently for many of our problems, sometimes people tends to use triggers for those, but remember trigger is not a good option in most of the time. You may want to read this article from Tom Kyte regarding trigger : Ask Tom: The Trouble with Triggers..
I will describe two scenarios, first we have a table with whole_name column, So we want to restrict bad entries in that column and we want to allow only alphabet and space as name can not be alphanumeric or purely numeric!
Second, we have a table with first_name and last_name column, So we want to allow only alphabet here, no numeric and no space.
This SQL101 is related to check constraint. We can implement check constraint very efficiently for many of our problems, sometimes people tends to use triggers for those, but remember trigger is not a good option in most of the time. You may want to read this article from Tom Kyte regarding trigger : Ask Tom: The Trouble with Triggers..
I will describe two scenarios, first we have a table with whole_name column, So we want to restrict bad entries in that column and we want to allow only alphabet and space as name can not be alphanumeric or purely numeric!
Second, we have a table with first_name and last_name column, So we want to allow only alphabet here, no numeric and no space.
SCOTT@orclSB 28-JAN-17> --My database version.
SCOTT@orclSB 28-JAN-17> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf
1 -- In the whole_name column, we will only allow alphabet and space.
2 CREATE TABLE contacts
3 (
4 whole_name varchar2(30) constraint chk_whole_name check (regexp_like (whole_name,'^[[:alpha:][:space:]]+$'))
5* )
SCOTT@orclSB 28-JAN-17> /
Table created.
SCOTT@orclSB 28-JAN-17> --This should work.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik');
1 row created.
SCOTT@orclSB 28-JAN-17> --This should not work as 123 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik123');
insert into contacts values ('Saubhik123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_WHOLE_NAME) violated
SCOTT@orclSB 28-JAN-17> --This should not work as 1234 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('1234');
insert into contacts values ('1234')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_WHOLE_NAME) violated
SCOTT@orclSB 28-JAN-17> --This should work as only alphabet and space.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik Banerjee');
1 row created.
SCOTT@orclSB 28-JAN-17> drop table contacts purge;
Table dropped.
SCOTT@orclSB 28-JAN-17> ed
Wrote file afiedt.buf
1 -- Now, there is no whole_name column, instead we have broken down as first_name and last_name.
2 -- So, Only alphabet should be allowed, no numeric character and no space.
3 CREATE TABLE contacts
4 (
5 first_name varchar2(30) constraint chk_first_name check (regexp_like (first_name,'^[[:alpha:]]+$'))
6* )
SCOTT@orclSB 28-JAN-17> /
Table created.
SCOTT@orclSB 28-JAN-17> --This should work.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik');
1 row created.
SCOTT@orclSB 28-JAN-17> --This should not work as 123 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik123');
insert into contacts values ('Saubhik123')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated
SCOTT@orclSB 28-JAN-17> --This should not work as 1234 is numeric.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('1234');
insert into contacts values ('1234')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated
SCOTT@orclSB 28-JAN-17> --This should not work as space is not allowed within first_name.
SCOTT@orclSB 28-JAN-17> insert into contacts values ('Saubhik Banerjee');
insert into contacts values ('Saubhik Banerjee')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHK_FIRST_NAME) violated
SCOTT@orclSB 28-JAN-17> drop table contacts purge;
Table dropped.
SCOTT@orclSB 28-JAN-17>
