[oracle] How can I drop a "not null" constraint in Oracle when I don't know the name of the constraint?

I have a database which has a NOT NULL constraint on a field, and I want to remove this constraint. The complicating factor is that this constraint has a system-defined name, and that constraint's name differs between the production server, integration server, and the various developer databases. Our current process is to check in change scripts, and an automated task executes the appropriate queries through sqlplus against the target database, so I'd prefer a solution that could just be sent straight into sqlplus.

On my own database, the SQL to drop this would be:

alter table MYTABLE drop constraint SYS_C0044566

I can see the constraint when I query the all_constraints view:

select * from all_constraints where table_name = 'MYTABLE'

but I am not sure how to work with the SEARCH_CONDITION's LONG data type or how best to dynamically delete the looked-up constraint even after I know its name.

So, how can I create a change script that can drop this constraint based on what it is, rather than what its name is?


EDIT: @Allan's answer is a good one, but I am concerned (in my lack of Oracle expertise) that it may not be universally true that any constraint that might have a system-generated name will have associated with it a way to remove the constraint without having to know its name. Is it true that there will always be a way to avoid having to know a system-named constraint's name when logically dropping that constraint?

This question is related to oracle plsql constraints

The answer is


Try:

alter table <your table> modify <column name> null;

To discover any constraints used, use the code below:

-- Set the long data type for display purposes to 500000.

SET LONG 500000

-- Define a session scope variable.

VARIABLE output CLOB

-- Query the table definition through the <code>DBMS_METADATA</code> package.

SELECT dbms_metadata.get_ddl('TABLE','[Table Described]') INTO :output FROM dual;

This essentially shows a create statement for how the referenced table is made. By knowing how the table is created, you can see all of the table constraints.

Answer taken from Michael McLaughlin's blog: http://michaelmclaughlin.info/db1/lesson-5-querying-data/lab-5-querying-data/ From his Database Design I class.


I was facing the same problem trying to get around a custom check constraint that I needed to updated to allow different values. Problem is that ALL_CONSTRAINTS does't have a way to tell which column the constraint(s) are applied to. The way I managed to do it is by querying ALL_CONS_COLUMNS instead, then dropping each of the constraints by their name and recreate it.

select constraint_name from all_cons_columns where table_name = [TABLE_NAME] and column_name = [COLUMN_NAME];


Just remember, if the field you want to make nullable is part of a primary key, you can't. Primary Keys cannot have null fields.


If constraint on column STATUS was created without a name during creating a table, Oracle will assign a random name for it. Unfortunately, we cannot modify the constraint directly.

Steps involved of dropping unnamed constraint linked to column STATUS

  1. Duplicate STATUS field into a new field STATUS2
  2. Define CHECK constraints on STATUS2
  3. Migrate data from STATUS into STATUS2
  4. Drop STATUS column
  5. Rename STATUS2 to STATUS

    ALTER TABLE MY_TABLE ADD STATUS2 NVARCHAR2(10) DEFAULT 'OPEN'; ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_CHECK_STATUS CHECK (STATUS2 IN ('OPEN', 'CLOSED')); UPDATE MY_TABLE SET STATUS2 = STATUS; ALTER TABLE MY_TABLE DROP COLUMN STATUS; ALTER TABLE MY_TABLE RENAME COLUMN STATUS2 TO STATUS;


Something like that happened to me when I made copies of structures to temporary tables, so I removed the not null.

DECLARE
   CURSOR cur_temp_not_null IS
        SELECT table_name, constraint_name  FROM all_constraints WHERE table_name LIKE 'TEMP_%' AND  owner='myUSUARIO';

   V_sql VARCHAR2(200); 

BEGIN
  FOR c_not_null IN cur_temp_not_null
   LOOP
     v_sql :='ALTER TABLE ' || c_not_null.table_name || ' DROP CONSTRAINT '|| c_not_null.constraint_name;
     EXECUTE IMMEDIATE  v_sql;     
  END LOOP;
END;

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to plsql

How can you tell if a value is not numeric in Oracle? Why do I get PLS-00302: component must be declared when it exists? Split function in oracle to comma separated values with automatic sequence PLS-00428: an INTO clause is expected in this SELECT statement What is the max size of VARCHAR2 in PL/SQL and SQL? PLS-00201 - identifier must be declared Default Values to Stored Procedure in Oracle How to call Oracle MD5 hash function? Proper way of checking if row exists in table in PL/SQL block PLS-00103: Encountered the symbol when expecting one of the following:

Examples related to constraints

How to trap on UIViewAlertForUnsatisfiableConstraints? trying to animate a constraint in swift Remove all constraints affecting a UIView "Insert if not exists" statement in SQLite Unique Key constraints for multiple columns in Entity Framework SQL Server 2008- Get table constraints How to remove constraints from my MySQL table? Creating layout constraints programmatically Display names of all constraints for a table in Oracle SQL Add primary key to existing table