1

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.

Andre Lombaard
  • 6,985
  • 13
  • 55
  • 96
  • 7
    No, there's no "magic" way to do this. If you use the visual designer in SSMS, it will basically re-create the table with the new column order, copy the data over, and drop the old table. But ***why*** is the column order so important to you? In the relational model, you should **not** rely on any particular order - the order should be without significance, really – marc_s Sep 13 '13 at 08:49
  • I agree, the order is of no significant importance, the database is still in the design phase and this is simply a matter of OCD :) If this was an existing database already deployed in a production environment I would have added the column to the end of the table. Was just curious if there is 'magic' way. – Andre Lombaard Sep 13 '13 at 10:21

1 Answers1

2

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

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676