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 LIKEmy_table
AND constraint_type = ‘C’;
CURSOR search IS
SELECT search_condition
FROM user_constraints
WHERE table_name LIKEmy_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!

Advertisements

One thought on “Drop that unknown NOT NULL constraint – Part 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s