none
Alter Query for column with constraints

    Question

  • Hi,

    I am having a primary key column in a table which is bigint. This column is referred as foriegn key 5 more tables. Now I want to change the datatype of this primary key column to varchar. When I used alter query, it throws me error saying some constraints are used on it. How can we change the datatype of this column without affecting all those constraints?

    Thanks & Regards,
    Subalaksmi Vijayarajan.

    Thursday, April 29, 2010 1:07 PM

Answers

  • Hi, I think you cannot do this. If you change the type of the primary key you must change the type of all foriegn keys too. IF I understant correctly the problem you can do something like this:

    - Remove the foreign keys from the other table

    - change the type ok primary key

    - Reinsert the foreign key

    Hope it helps

    Nicola

    • Proposed as answer by Naomi NModerator Thursday, April 29, 2010 1:33 PM
    • Marked as answer by KJian_ Wednesday, May 05, 2010 8:06 AM
    Thursday, April 29, 2010 1:23 PM

All replies

  • Hi, I think you cannot do this. If you change the type of the primary key you must change the type of all foriegn keys too. IF I understant correctly the problem you can do something like this:

    - Remove the foreign keys from the other table

    - change the type ok primary key

    - Reinsert the foreign key

    Hope it helps

    Nicola

    • Proposed as answer by Naomi NModerator Thursday, April 29, 2010 1:33 PM
    • Marked as answer by KJian_ Wednesday, May 05, 2010 8:06 AM
    Thursday, April 29, 2010 1:23 PM
  • Hi Nicola,

    Can we not achieve this using NOCHECK constraint statement in the alter query?

    Thanks & Regards,
    Subalakshmi Vijayarajan.

    Thursday, April 29, 2010 1:29 PM
  • Hi, the problem is that if you change the data type of the primary key, you must change the data type of alla the foreign keys too. If you try to assign a foreign key that not have  the same data type of the primary key sqlserver say something like this:

    Column 'prodotti.ID' is not the same data type as referencing column 'PRODOTTI_DETTAGLIO.IDPROD' in foreign key 'FK_prodotti'.

    So I think is not so simple change the primary key data type without change the data type of the foreign key.

    I hope I explain myself, I don't speak english very well :)

    Nicola

    Thursday, April 29, 2010 2:20 PM