CODEWITHSUNDEEP
sqlsql-server-2012ssms
Andre Lombaard
Andre Lombaard

Reputation: 7105

Change table column order while preserving the foreign key constraint

I have a table in a SQL server 2012 database table with the following columns

Id (PK)
Name 
UserId (FK)
Created
Updated

I want to change the order of the columns by swapping the Name and UserId to

Id (PK)
UserId (FK)
Name
Created
Updated

Please note that the table do not contain any data at the moment. Usually I do not bother with writing ALTER scripts and simply use SQL Management Studio to create and modify my tables. I would usually simply use the DROP AND CREATE TO menu option to re create the table and modify the order of the columns, but in this case the foreign key constraint is preventing me from doing this successfully.

Is there any way that I can change the order of the database columns while preserving the foreign key constraint? The only other solution I can see is to remove the foreign key constraint, re create the table and add the foreign key constraint again.

Upvotes: 1

Views: 828

Answers (1)

gbn
gbn

Reputation: 432421

The on-disk order is different (and has a fixed method of doing it) to the apparent column order in the CREATE. It makes no difference.

That is, sys.columns.column_id order does not match on-disk order

Upvotes: 2

Related Questions