Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: Define default column values on existing FK

    Is it possible to alter a table with a FK that at the moment is nullable and has null values for that FK to not nullable with a default value.

    For instance , by updating the null values to the proper default as step 1 and then issueing the ALTER statement to reset the column properties?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You would like to update a child table, right? If so, make sure that parent table contains all values you are about to insert into a child tables' column. Once all NULL values are replaced with some other value, alter this table and modify the column to NOT NULL. There's no need to do anything with the foreign key constraint.

    Or did I not understand your question?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by blom0344
    Is it possible to alter a table with a FK that at the moment is nullable and has null values for that FK to not nullable with a default value.
    Sure:
    Code:
    UPDATE theTable 
        SET fk_column = the_default_value
    WHERE fk_column IS NULL;
    COMMIT;
    
    ALTER TABLE theTable 
       MODIFY fk_column NOT NULL DEFAULT the_default_value;
    the syntax is out of my head, I didn't check if it's all correct, but you get the idea I guess.

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Thanks, I did some testing and this seems to be no problem.
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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