#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2006
    Posts
    3
    Rep Power
    0

    Remove NOT NULL on a column


    Hi All !

    I have a problem.
    First of all, I have a table called TOCCUPATION
    In this table, I've got a field called STRUCTUREPARETAPE
    This field is NOT NULL (and it is a foreign key that references the table called TSTRUCTUREPARETAPE)

    Now I change this field and I remove the NOT NULL by doing this :
    update RDB$RELATION_FIELDS set
    RDB$NULL_FLAG = NULL
    where (RDB$FIELD_NAME = 'STRUCTUREPARETAPE') and
    (RDB$RELATION_NAME = 'TPOSTEPARETAPE')

    Unfortunately, I can't do an insert with a null value for this field.

    I tried to create a new domain for the STRUCTUREPARETAPE field but it doesn't work.

    Some idea ?

    Thank you !
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2006
    Posts
    3
    Rep Power
    0
    Sure but I need this modification.
    This is the way I proceed and it's not secure at all because you have to know what you do but everything seems alright.

    I dropped every constraints and procedures
    I copied the data of the column in a temporary table
    I dropped the column
    I recreate the column without the not null constraint
    I copied data from the temp table to the official table
    I recreate every constraint and procedures

    It is 'du bidouillage' as we say here, in belgium but that was the only way I find.
    Hope to avoid troubles
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    You can drop the constraint that is connected to the NOT NULL definition. To find out the constraint name, use the following query:
    Code:
    SELECT r.rdb$constraint_name
    FROM rdb$relation_constraints r,
         rdb$check_constraints c
    WHERE r.rdb$constraint_name = c.rdb$constraint_name
    AND   r.rdb$relation_name = 'TOCCUPATION'
    AND   c.rdb$trigger_name = 'STRUCTUREPARETAPE'
    AND   r.rdb$constraint_type = 'NOT NULL';
    Then use the name that is returned from that query to drop the constraint. Assuming the constraint is named INTEG_7:
    Code:
    ALTER TABLE toccupation DROP CONSTRAINT INTEG_7;
    COMMIT;
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2006
    Posts
    3
    Rep Power
    0
    ok, thank you
    I'll try this way.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    249
    Rep Power
    14
    Shammat, I don't think that's all. You actually need to modify the RDB$NULL_FLAG on RDB$FIELDS as well.


    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com

IMN logo majestic logo threadwatch logo seochat tools logo