Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: adding not null constraint to an existing column

    Hi,

    I have a table with 3 columns..say col1, col2, col3. Now i want to add primary key to col1 which does not have NOT NULL constraint. how to do this?
    I don't want to add or delete any new or existing columns.

    thanks in advance.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I want to lose weight, but I don't want to stop eating or start exercising.


    If you don't want to alter your existing table, can't you create a new one (with NOT NULL for col1), create PK and load/import data from your existing table.

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    1. SELECT COUNT(*) FROM your_table WHERE COL1 IS NOT NULL
    2. If the above query returns anything else than 0 stop
    3. ALTER TABLE your_table ALTER COLUMN col1 SET NOT NULL
    4. ALTER TABLE your_table ADD CONSTRAINT your_table_PK PRIMARY KEY (col1)
    5. REORG TABLE your_table
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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