Good Practices for Creation Scripts - Part 1

When you're working with small databases with just a few tables, everything is handled very fast and easy.

But what happens when our small application starts to grow and some modules appear and require more and more tables? Why not start doing things the right way to avoid future "complete reconstructions" due to incompatibilities?

So, here are some tips:

• For each table, always have a code (2 to 4 chars)

employee --> emp
department --> dep
title --> ttl
works_in --> wrk

• Each column in the table must contain the code of its parent table. Foreign keys will have the referenced table code.

In table employee:
id --> emp_id
name --> emp_name
title --> ttl_id

• Always write complete creation scripts. Avoid using simplified coding style, because it will generate constraints with system-generated names, hard to find and identify later on. When a constraint is violated, it’s good to have it named to avoid encounters with messages like “Constraint SYS00013292384 violated”.
  

I'll present 2 ways to create a very small employee database, make your own conclusions:

The short way:

CREATE TABLE title (
id NUMBER(10) PRIMARY KEY,
desc VARCHAR2(50) NOT NULL
);

CREATE TABLE employee (
id NUMBER(12) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
title NUMBER(10) NOT NULL
);
ALTER TABLE employee ADD FOREIGN KEY (title) REFERENCES title (id);

CREATE TABLE department (
id NUMBER(2) PRIMARY KEY,
name VARCHAR2(50) NOT NULL
);

CREATE TABLE works_in (
emp NUMBER(12) NOT NULL,
dep NUMBER(2) NOT NULL
);
ALTER TABLE works_in ADD PRIMARY KEY (emp,dep);
ALTER TABLE works_in ADD FOREIGN KEY (emp) REFERENCES employee (id);
ALTER TABLE works_in ADD FOREIGN KEY (dep) REFERENCES department (id);

The right way (for me) to save us trouble in the future (and start making good coding habits):

CREATE TABLE title
(
ttl_id NUMBER(2),
ttl_name VARCHAR(50)
);

ALTER TABLE title ADD CONSTRAINT "PK_ttl" PRIMARY KEY (ttl_id);


CREATE TABLE employee
(
emp_id NUMBER(12),
emp_name VARCHAR(50),
ttl_id NUMBER(2),
);

ALTER TABLE employee
ADD CONSTRAINT "PK_emp" PRIMARY KEY (emp_id);
ALTER TABLE employee
ADD CONSTRAINT "NN_emp_name" CHECK (emp_name IS NOT NULL);
ALTER TABLE employee
ADD CONSTRAINT "FK_emp_ttl" FOREIGN KEY (ttl_id)
REFERENCES title (ttl_id);


CREATE TABLE department
(
dep_id NUMBER(2),
dep_name VARCHAR(25),
dep_location VARCHAR(50)
);

ALTER TABLE department
ADD CONSTRAINT "PK_dep" PRIMARY KEY (dep_id);

CREATE TABLE works_in
(
emp_id NUMBER(12),
dep_id NUMBER(2)
);

ALTER TABLE works_in
ADD CONSTRAINT "PK_wrk" PRIMARY KEY (emp_id, dep_id);

ALTER TABLE works_in
ADD CONSTRAINT "FK_wrk_emp" FOREIGN KEY (emp_id)
REFERENCES employee (emp_id);

ALTER TABLE works_in
ADD CONSTRAINT "FK_wrk_dep" FOREIGN KEY (dep_id)
REFERENCES department (dep_id);

If you use these simple tips, you’ll not only work in a more organized way, you’ll be more efficient when solving problems over databases with these kind of implementation.

Once again, I’m not telling this is THE WAY to do things.  Just one way that works for me.  Comments are always welcome.

Drop that unknown NOT NULL constraint - Part 2

Recently I got word from someone at a Database Forum pointing that it would be easier to join a couple tables to get the name of the constraint we want to drop.

The code is this:

SELECT c.constraint_name
FROM user_tab_columns t, user_cons_columns c
WHERE t.table_name = my_table
AND t.table_name = c.table_name
AND t.column_name = my_column
AND t.nullable = 'N';

That's it, we got now the constraint name and can drop it easely.

That would shorten the script a lot, tho the performance will probably depend on various details as the number of rows per table and the position of the value we look up.

So, the new script would look as follows:

DECLARE
constname user_cons_columns.constraint_name%TYPE;
BEGIN
SELECT c.constraint_name INTO constname
FROM user_tab_columns t, user_cons_columns c
WHERE t.table_name = my_table
AND t.table_name = c.table_name
AND t.column_name = my_column
AND t.nullable = 'N';

EXECUTE IMMEDIATE 'ALTER TABLE my_table DROP CONSTRAINT ' || 'constname';
dbms_output.put_line('Constraint '|| constname || ' dropped successfuly!');
COMMIT;
END;

Nevertheless and despite the obvious change in the size and performance of this script, during the test, I found something interesting: it fails if the constraint is disabled, due to the fact that user_tab_columns.nullable contains the current value of the system itself and not the one from the constraint.

Drop that unknown NOT NULL constraint - Part 1

Is very natural for any DBA to create tables through the ol' good DDL statement:

CREATE TABLE sysuser (
login VARCHAR2(20) PRIMARY KEY,
pswd VARCHAR2(20) NOT NULL
);

But that generates later problems because the DBMS will name those constraints as "SYSXXXXX", as I have discovered recently in my job. Some, will instantly say:
-Hey, wait. For that PK I use the ALTER TABLE statement and add a name like "PK_sysuser" to the constraint for better understanding of later raisen errors.

Well, yes. That's right, but we almost never take the time to add custom names to NOT NULL constraints. That's exactly the problem here.

Some time ago, a table was created (in development environment and later on in production), with its constraints and everything was fine until yesterday. A developer came to me, asking if it's posible to drop that constraint "automatically" since we don't have access to the production environment. The answer was clear, if it exists... It can be dropped. More so, the Data Dictionary got all the necesary information to do it. So it should be a quick and easy task to accomplish.

What I got:

• Table & field names

What I need:

• Find the name of the constraint
• Drop the constraint
  

I first used:

SELECT *
FROM user_constraints
WHERE table_name = my_table;

Got a bunch of data and got the search_condition of the constraint as "my_field" IS NOT NULL. And I thought - Job done! All I have to do is refine my query to use this field and I'll get the name of the constraint I want to drop; so it would be a simple PL/SQL block of about a half dozen lines.

So my fingers ran through the keyboard writing:

SELECT constraint_name
FROM user_constraints
WHERE search_condition = '"my_field" IS NOT NULL';

- AHA!!! Oracle error: ORA-00997!!!

So I googled a bit and found that LONG (datatype) fields can't be used in WHERE clauses!!
And, as Murphy's Laws dictates, search_condition is a LONG field.

To shorten this up, I'll say that I took some workarounds and finished creating this script:

SET SERVEROUTPUT ON;

DECLARE
aux user_constraints.search_condition%TYPE;
condition user_constraints.search_condition%TYPE;
constname user_constraints.constraint_name%TYPE;
CURSOR names IS
SELECT constraint_name
FROM user_constraints
WHERE table_name LIKE 'my_table'
AND constraint_type = 'C';
CURSOR search IS
SELECT search_condition
FROM user_constraints
WHERE table_name LIKE 'my_table'
AND constraint_type = 'C';
BEGIN
OPEN names;
OPEN search;

condition:= '"my_field" IS NOT NULL';
dbms_output.put_line('Contrained column is: my_field');

LOOP
FETCH names INTO constname;
FETCH search INTO aux;

dbms_output.put_line('Constraint name is: '||constname);
dbms_output.put_line('Search condition is: '|| aux);

IF aux = condition THEN
dbms_output.put_line('Selected constraint to drop: '|| constname);
END IF;

EXIT WHEN aux = condition;
END LOOP;

CLOSE names;
CLOSE search;
EXECUTE IMMEDIATE ('ALTER TABLE my_table DROP CONSTRAINT ' || constname);
COMMIT;
dbms_output.put_line('Constraint dropped succesfully');

END;

I hope this helps some of you, and I'm not stating here this is the best way to do it. It's just one way to do it. I even created a Stored procedure with table_name and column_name as INPUT parameters that will drop any "unknown" NOT NULL constraint from any table and any field.

All comments are welcome!