Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Location
    Caracas, Venezuela
    Posts
    4

    Post DROP a system-generated NOT NULL constraint

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

    Code:
    CREATE TABLE sysuser (
        login VARCHAR2(20) PRIMARY KEY,
        pswd VARCHAR2(20) NOT NULL
    );
    Thanks to TheDuck for pointing a syntax error I had.
    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:

    Code:
    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:
    Code:
    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:
    Code:
    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!
    Last edited by Silvarion; 08-20-09 at 16:15.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    (your create table statement is syntactically incorrect)

    you have a few constraints yet you don't mention which column you have issue with.

    one is a primary key constraint (unique and not null) - login column
    second is a not null constraint - pswd column

    to drop first: alter table tablename drop primary key
    to drop second: alter table tablename modify columnname not null;

    PHP Code:
    duck@db1create table sysuser 
    login VARCHAR2(20PRIMARY KEYpswd VARCHAR2(20NOT NULL);

    Table created.

    duck@db1desc sysuser
     Name                                   Null
    ?    Type
     
    -------------------------------------- -------- --------------------------
     
    LOGIN                                  NOT NULL VARCHAR2(20)
     
    PSWD                                   NOT NULL VARCHAR2(20)

    duck@db1alter table sysuser drop primary key;

    Table altered.

    duck@db1desc sysuser
     Name                                   Null
    ?    Type
     
    -------------------------------------- -------- --------------------------
     
    LOGIN                                           VARCHAR2(20)
     
    PSWD                                   NOT NULL VARCHAR2(20)

    duck@db1alter table sysuser modify pswd null;

    Table altered.

    duck@db1desc sysuser
     Name                                   Null
    ?    Type
     
    -------------------------------------- -------- --------------------------
     
    LOGIN                                           VARCHAR2(20)
     
    PSWD                                            VARCHAR2(20
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2009
    Location
    Caracas, Venezuela
    Posts
    4
    Greetings, first of all. The tabler creation was an example. We're talking about a table in a production environment that I can't post here. The fact is that there IS a NOT NULL constraint (system-generated name) and we need to drop it.

    Now, for the interesting part. I didn't know that the table could be altered just like this.

    Code:
    duck@db1> alter table sysuser modify pswd null;
    Now, my question would be... This drops the constraint or just change the search_condition within the user_constraints table?

    Now, as for the script, it can be used also as an example to find a workaround for that ora-00997 error.

    And that was just the meaning of this thread.

    By the way, what DBMS are you using? Version?
    Just took the time to try your code and I get the error ORA-01451
    Code:
    ORA-01451:
    	column to be modified to NULL cannot be modified to NULL
    Cause: 	the column may already allow NULL values,
    the NOT NULL constraint is part of a primary key or check constraint.
    Action: 	if a primary key or check constraint is enforcing the NOT NULL 
    constraint, then drop that constraint.
    Thanks a lot for the info on the syntaxis (I'll fix it now) and for taking your time to read my post.
    Last edited by Silvarion; 08-20-09 at 17:47.

  4. #4
    Join Date
    Feb 2009
    Posts
    62
    Two things.

    1) your SQL for creating the table is just lazy. If you bother to explicitly add constraint names into the Create table script, then your whole problem goes away:
    Code:
    CREATE TABLE test_049 (
        col_1 VARCHAR2(20) constraint test_049_pk PRIMARY KEY,
        col_2 VARCHAR2(20) constraint test_049_nn01 NOT NULL,
        col_3 varchar2(20) not null
    );
    2) Your problem is (if I read it correctly) getting the constraint names for system generated not null constraints.
    Not null constraints are a special case, and details of which columns are not nullable are also held in USER_TAB_COLUMNS as well as USER_CONSTRAINTS and USER_CONS_COLUMNS.

    So, just join USER_TAB_COLUMNS to USER_CONS_COLUMNS and away you go:
    Code:
    select c.table_name,c.column_name,c.constraint_name
    from   user_tab_columns t
          ,user_cons_columns c
    where t.table_name = 'TEST_049'
    and   t.table_name = c.table_name
    and   t.column_name = c.column_name
    and   t.nullable = 'N';

  5. #5
    Join Date
    Aug 2009
    Location
    Caracas, Venezuela
    Posts
    4
    Thanks a lot JRowbottom, that was REALLY helpful!!

    But that so called "lazy" SQL was not my fault... I just got on this job and there are LOTS of problems with DB standards and constraints. I've even found tables without a PK (and really require it).

    Your query on the other hand can simplify the script to find the constraint name!!

    Keep it up! And thanks again!
    Last edited by Silvarion; 08-21-09 at 10:10.

  6. #6
    Join Date
    Aug 2009
    Location
    Caracas, Venezuela
    Posts
    4
    Hello again JRowBottom, I just wanted to tell you that despite the obvious simplicity of your solution, I've been doing some tests and it fails in the case that the constraint is disabled, 'cause the user_tab_columns.nullable field contains the actual system state for that constraint, and not the one on the constraint itself.

    So if we can asure that the constraint we want to drop is enabled, your solution is perfect... If we can't, I'd stick with mine.
    Last edited by Silvarion; 08-22-09 at 02:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •