How can I drop a not null constraint in Oracle when I don t know the name of the constraint

Question

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

User · Answer

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  lt code gt DBMS METADATA lt  code gt  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

User · Answer

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   Duplicate STATUS field into a new field STATUS2  Define CHECK constraints on STATUS2 Migrate data from STATUS into STATUS2 Drop STATUS column 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

User · Answer

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

User · Answer

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

User · Answer

Try   alter table  lt your table gt  modify  lt column name gt  null

User · Answer

alter table MYTABLE modify  MYCOLUMN null     In Oracle  not null constraints are created automatically when not null is specified for a column   Likewise  they are dropped automatically when the column is changed to allow nulls   Clarifying the revised question  This solution only applies to constraints created for  not null  columns  If you specify  Primary Key  or a check constraint in the column definition without naming it  you ll end up with a system-generated name for the constraint  and the index  for the primary key   In those cases  you d need to know the name to drop it  The best advice there is to avoid the scenario by making sure you specify a name for all constraints other than  not null    If you find yourself in the situation where you need to drop one of these constraints generically  you ll probably need to resort to PL SQL and the data-definition tables

User · Answer

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

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

Examples related to oracle

Examples related to plsql

Examples related to constraints