Working with some Oracle Constraints
I was working with some code and wanted to add a Check Constraint for my test database. Using Oracle 10g I looked up some syntax reference and lo and behold I found Regular Expressions staring right at me!
Quick side note : Coming from a programmers point of view I have to say the Oracle's Database is the most powerful and flexible database I have ever dealt with. You can write ANSI SQL, Oracle flavor SQL, and PL/SQL (not counting using Java for procedure language too). Also, every modern programming language works against it!
Heck go to http://www.monster.com and search on Oracle - you'll get OVER 1000 postings. Its a great database and their is a REAL shortage of professionals who can work with it.
Anyway, back to my simple constraint. Like I said earlier, Oracle does something I like - they support Regular Expressions in their SQL. Regular Expressions basically allow you to pattern match data to determine if it is what you are looking for for validation, etc. If you want to know more, check out http://www.regular-expressions.info/ or http://www.dba-oracle.com/t_regular_expressions.htm Both sites are great for getting your learn on!
So I did this first:
CREATE TABLE Customer
(
CustomerID int not null,
Last_Name varchar(20) not null,
First_Name varchar(20) not null,
Primary Key (CustomerID)
);
ALTER TABLE CUSTOMER
ADD CONSTRAINT cust_f_name_chk
CHECK(REGEXP_LIKE(first_name,'^[:alpha:]'));
The real magic here is in the check constraint's REGEXP_LIKE( ) function. This is telling the database that only ALPHA characters - A through Z (regardless of case) are allowed.
So the following will error out:
INSERT INTO Customer (CustomerID,Last_Name,First_Name)
VALUES (1,'Valverde','Michael5');
but this will work:
INSERT INTO Customer (CustomerID,Last_Name,First_Name)
VALUES (2,'Valverde','Michael');
Anyway, I can now be assured GIGO (Garbage In/Gospel Out) doesn't happen with my First_Name field. I'll add the same to the other fields since I don't want single quotes, special characters, etc in my data either.
And one last thing, if you want to add this to a table that already has data and you want to accept what is in has to be left alone, just add the NOVALIDATE clause to the ADD Constraint and it will apply to the table but leave existing data alone. Example here:
ALTER TABLE CUSTOMER
ADD CONSTRAINT cust_f_name
CHECK(REGEXP_LIKE(first_name,'^[:alpha:]'))NOVALIDATE;
Hope this helps someone!
Till later
Quick side note : Coming from a programmers point of view I have to say the Oracle's Database is the most powerful and flexible database I have ever dealt with. You can write ANSI SQL, Oracle flavor SQL, and PL/SQL (not counting using Java for procedure language too). Also, every modern programming language works against it!
Heck go to http://www.monster.com and search on Oracle - you'll get OVER 1000 postings. Its a great database and their is a REAL shortage of professionals who can work with it.
Anyway, back to my simple constraint. Like I said earlier, Oracle does something I like - they support Regular Expressions in their SQL. Regular Expressions basically allow you to pattern match data to determine if it is what you are looking for for validation, etc. If you want to know more, check out http://www.regular-expressions.info/ or http://www.dba-oracle.com/t_regular_expressions.htm Both sites are great for getting your learn on!
So I did this first:
CREATE TABLE Customer
(
CustomerID int not null,
Last_Name varchar(20) not null,
First_Name varchar(20) not null,
Primary Key (CustomerID)
);
ALTER TABLE CUSTOMER
ADD CONSTRAINT cust_f_name_chk
CHECK(REGEXP_LIKE(first_name,'^[:alpha:]'));
The real magic here is in the check constraint's REGEXP_LIKE( ) function. This is telling the database that only ALPHA characters - A through Z (regardless of case) are allowed.
So the following will error out:
INSERT INTO Customer (CustomerID,Last_Name,First_Name)
VALUES (1,'Valverde','Michael5');
but this will work:
INSERT INTO Customer (CustomerID,Last_Name,First_Name)
VALUES (2,'Valverde','Michael');
Anyway, I can now be assured GIGO (Garbage In/Gospel Out) doesn't happen with my First_Name field. I'll add the same to the other fields since I don't want single quotes, special characters, etc in my data either.
And one last thing, if you want to add this to a table that already has data and you want to accept what is in has to be left alone, just add the NOVALIDATE clause to the ADD Constraint and it will apply to the table but leave existing data alone. Example here:
ALTER TABLE CUSTOMER
ADD CONSTRAINT cust_f_name
CHECK(REGEXP_LIKE(first_name,'^[:alpha:]'))NOVALIDATE;
Hope this helps someone!
Till later
Comments