Search code examples
sqlalter

Altering column on two tables simultaneously

I have linked tables, players and playerRegSeason. They are linked by a playerID which is the primary key for players and is in a strange non-numerical form. I want to make it so all new entries have unique auto incremented playerIDs. If I alter the column to be auto incrementing it changes all the current playerIDs, which I'd be ok with, but I lose the relationsihp to the playerRegSeason. Is there a way to alter the playerID column so it will automatically update in playerRegSeason?

Solution

  • Usually you would be better off doing it in stages.

    1. Create a new column with the auto-increment to the players table and populate it.
    2. Add column to the playerRegSeason allow it to be NULL initially
    3. Update the new column in playerRegSeason to point to the new playerID column.
    4. Alter the column in playerRegSeason to not allow nulls
    5. Add a new foreign constraint to playerRegSeason
    6. Drop the old foreign constraint
    7. Drop the old ID & foreign fields from both tables.

    EDIT: Elaboration of step 3 as requested

    UPDATE playerRegSeason s
INNER JOIN players p ON s.playerID = p.playerID
SET s.NewPlayerID =  p.NewPlayerID

    However the exact syntax may vary depending on the version of SQL