1

I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout 

table_name( Field_1  datatype PK, Field_2 datatype,....)

and I want the inserted column, say Field_k to be part of a PK jointly with the existing single-field PK.

I haven't found any source for how to do this or for whether it is possible or not. Any advice, please?

|improve this question
  • 1
    You need to drop the FKs that reference it, then drop the PK and re-create it, then re-create the FK. There's no magic syntax for this. – Aaron Bertrand♦ Jun 6 '16 at 19:27
  • Thanks, would I get a warning if I am (unknowingly, of course) violating some constraints in the process, i.e., if I forget to drop some referenced table? Just curious as to how SQL server handles integrity/constraint violations. – user89723 Jun 6 '16 at 19:32
  • Yes, you won't be able to drop the PK while any FK still references it. And of course you won't be able to re-create the FK until those other tables also have a column that can fill in for the new column you're adding to the PK. – Aaron Bertrand♦ Jun 6 '16 at 19:32
  • Excellent, do I write this as an answer (with due credit to you) or would you like to write it yourself? – user89723 Jun 6 '16 at 19:34
6

You need to drop the FKs that reference it, then drop the PK and re-create it, then re-create the FK. There's no magic syntax for this, but you can get some ideas here for how to generate the drop/create scripts for the FKs:

would I get a warning if I am (unknowingly, of course) violating some constraints in the process, i.e., if I forget to drop some referenced table? Just curious as to how SQL server handles integrity/constraint violations.

You won't be able to drop the PK while any FK still references it. And of course you won't be able to re-create the FK until those other tables also have a column that can fill in for the new column you're adding to the PK.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.