none
How to reorder a column in a table which also has a foreign key

Answers

All replies

  • You can not change the order of columns in a table and what should it be good for? Create a view if you are looking for a specific order.

    You can only create a new table with the wanted order, copy data over to the new table, drop old table and rename new table to old name.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 02, 2016 1:20 PM
  • You need to recreate your table if you want to change the ordinal position of columns. The order of columns in a table is not significant and you can control the order of columns in your SELECT statement.
    Thursday, June 02, 2016 1:23 PM
    Moderator
  • Why do you need to change ordinal position of the column? You can always retrive data in the order you want by specifying columns in the select list in the desired order

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, June 02, 2016 1:41 PM
  • The correct order is needed to resolve my issue.

    Thursday, June 02, 2016 1:42 PM
  • How do I copy the data over to new table which will have different ordinal positions for the columns?  SSIS is one of the option I can think of but looking for suggestions.
    Thursday, June 02, 2016 1:44 PM
  • The correct order is needed to resolve my issue.

    I doubt it.

    Please provide your table DDL and what you want to. You can arrange column list in your select statement without touching the table column ordinal position.

    Thursday, June 02, 2016 1:48 PM
    Moderator
  • The correct order is needed to resolve my issue.

    I doubt it.

    Where did I mention that I need the order for my select statement?  
    Thursday, June 02, 2016 1:53 PM
  • Anyone with a solution?
    Thursday, June 02, 2016 2:05 PM
  • How do I copy the data over to new table which will have different ordinal positions for the columns

    By listing the column names explicit like

    INSERT INTO NewTable (Column1, Column2, ....)
    SELECT Column1, Column2, ....
    FROM oldTable


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Proposed as answer by s3l3ct Thursday, June 02, 2016 3:09 PM
    • Edited by Olaf HelperMVP Thursday, June 02, 2016 3:15 PM
    Thursday, June 02, 2016 2:21 PM
  • Use Olaf Helper's INSERT INTO statement. I work for a guy who insisted all the table columns be changed to alphabetical order. I feel your pain.

    After you copy/recreate the tables, drop the old tables and rename the new tables to the old names.


    • Edited by s3l3ct Thursday, June 02, 2016 3:07 PM
    Thursday, June 02, 2016 3:04 PM
  • The ordinal position of the columns in a table have no impact on anything in SQL Server.  SQL Server uses the column NAMEs to reference objects.

    If you must for some unknown reason change them, then your only solution is create a new table with the columns in the order you want, then run INSERT INTO newtable SELECT ... FROM oldtable, then drop the oldtable and rename the new table to the old name.

    Thursday, June 02, 2016 3:08 PM
    Moderator
  • The solution provided by Olaf Helper is not going to work since my original table has a PKey and FKey.
    Thursday, June 02, 2016 3:09 PM
  • If you must for some unknown reason change them, then your only solution is create a new table with the columns in the order you want, then run INSERT INTO newtable SELECT ... FROM oldtable, then drop the oldtable and rename the new table to the old name.

    It is not going to work since my original table has a PKey and FKey.
    Thursday, June 02, 2016 3:11 PM
  • You can't drop and recreate the PKey and FKey? Set insert identity to off and then back on? Do you have a maintenance window that can be scheduled for the changes?
    Thursday, June 02, 2016 3:13 PM
  • In that case, you would need to drop the FKs, and the recreate them after renaming the table.

    Rather than worry about the ordinal position which has no meaning, please tell us your actual problem and someone can help you resolve your actual problem.

    Thursday, June 02, 2016 3:33 PM
    Moderator

  • Rather than worry about the ordinal position which has no meaning, please tell us your actual problem and someone can help you resolve your actual problem.

    Yes, I have to worry about the ordinal position which has meaning to my application that is looking for very specific position for a field. The code cannot be modified, so, the only option I have is to modify the table.
    Thursday, June 02, 2016 4:00 PM
  • Why not using a view, here you can flexible define the order (and names) of the columns as you like without modifying the base table.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 02, 2016 5:35 PM

  • Rather than worry about the ordinal position which has no meaning, please tell us your actual problem and someone can help you resolve your actual problem.

    Yes, I have to worry about the ordinal position which has meaning to my application that is looking for very specific position for a field. The code cannot be modified, so, the only option I have is to modify the table.

    When you say app code look for specific position for a field, only thing come to my mind is the ORDER BY. in T-SQL you can use ordinal position of the column or column name or column alias in the ORDER BY. That could be the reason for creating new table?

    Thanks, hsbal

    Thursday, June 02, 2016 7:38 PM
  • You have some fundamental misconceptions about SQL. Tables in the schema have no ordering. Rows in a table have no ordering. Columns in a table have no ordering. The language is based on sets and uses names to reference data elements.

    If you wish to display columns in a particular order, you can put an explicit column list in your statement. If you depend on using "SELECT *" to get the default ordering from the system, then you are writing really bad SQL. You are depending on defaults, and not on your own code to handle things for you.

    In fact internally modern SQL engines completely rearrange the columns to take advantage of physical optimization on the disk. Most notably, the VARCHAR and NVARCHAR columns are put to the end of each physical record (a record is not a row!). This makes indexing easier and allows us to do displacements from the front of each record.

    If you really like just shuffling around lots of bits, then you can re-create the table under a new name with a specific default column ordering you wanted order that you wanted. But the engine will promptly ignore it and try to optimize each row.

    At one time, the columns in the primary key had to exactly match the columns in a foreign key, by count, position and datatype. Is that what you are trying to do?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, June 06, 2016 1:37 AM