How Does Oracle Make Development Easier? Continuing on the theme of late, what are the basic things that you can do to reduce the amount of code that needs to be written?

In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:

CREATE TABLE t
(
  id NUMBER(10)
    CONSTRAINT pk_id_t PRIMARY KEY,
  first_name VARCHAR2(30)
    CONSTRAINT nn_firstname_t NOT NULL,
  middle_name VARCHAR2(30),
  last_name VARCHAR2(40)
    CONSTRAINT nn_lastname_t NOT NULL,
  gender VARCHAR2(1)
    CONSTRAINT nn_gender_t NOT NULL
    CONSTRAINT ck_morf_gender_t CHECK ( gender IN ( 'M', 'F' ) ),
  ssn VARCHAR2(9)
    CONSTRAINT nn_ssn_t NOT NULL
    CONSTRAINT ck_9_ssn_t CHECK ( LENGTH( ssn ) = 9 )
    CONSTRAINT ck_numeric_ssn_t CHECK ( REGEXP_INSTR( ssn, ?, ?, ? ) )
    CONSTRAINT uq_ssn_t UNIQUE  
);

ID - is just a sequence generated key, no big deal there.
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.

This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.

This will give you much more reliable data, which as we all know, is the most important thing.

I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.

What kind of solutions do you have or do you use?

Labels: code, constraints, database, oracle, simplicity

  ¶ 3/27/2008 10:28:00 PM