SQLite Forum

Best way to handle unique column needs
Login
It's a bit hard to follow the exact requirement, but here are some ways of doing what I /think/ might be (at least partly) what you wanted to do.

It starts by creating an example old table that had a unique key and then fills it with a bit of data.

Then makes a new table with 2 Unique keys, one primary, one simply another standard unique key.

It then copies the old values to the new table using one direct copy and one Window-function example way of filling unique keys...

Then, imagining the new system is now running on its own, adds its own further rows with the PK automatically incrementing (Note: without "Autoincrement" being specified), and shows a way to fill out the second unique column with Unique keys after-the-fact. (Of course it should ideally be filled by the application directly).



```
  -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
  -- SQLite version 3.30.1  [ Release: 2019-10-10 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

CREATE TABLE Old_Table(OldUniqueID INTEGER PRIMARY KEY, OldField2, OldField3);

INSERT INTO Old_Table(OldField2, OldField3) VALUES
  ('John Smith','Tennis')
 ,('Joan Smith','Football')
 ,('James Smith','Snooker')
 ;

SELECT * FROM Old_Table;

  --  OldUniqueID|OldField2    |OldField3 
  -- ------------|-------------|----------
  --       1     |John Smith   |Tennis    
  --       2     |Joan Smith   |Football  
  --       3     |James Smith  |Snooker   


CREATE TABLE New_Better_Table(
  NewUniqueID INTEGER PRIMARY KEY,
  NewSecondaryUniqueKey INT UNIQUE,
  NewDefaultInt INT DEFAULT 5,
  NewField4,
  NewField5
);


INSERT INTO New_Better_Table(NewUniqueID, NewSecondaryUniqueKey, NewField4,NewField5)
SELECT OldUniqueID, row_number() OVER (PARTITION BY 1), OldField2, OldField3
  FROM Old_Table
;

SELECT * FROM New_Better_Table;

  --             |NewSecondar-|NewDefau-|             |          
  --  NewUniqueID| yUniqueKey |  ltInt  |NewField4    |NewField5 
  -- ------------|------------|---------|-------------|----------
  --       1     |      1     |    5    |John Smith   |Tennis    
  --       2     |      2     |    5    |Joan Smith   |Football  
  --       3     |      3     |    5    |James Smith  |Snooker   


-- Brand new rows adding (with PK incrementing automatically):

INSERT INTO New_Better_Table(NewField4,NewField5) VALUES
  ('John Jones','Badminton')
 ,('Joan Jones','Soccer')
 ,('James Jones','Pool')
 ;


SELECT * FROM New_Better_Table;

  --             |NewSecon-|         |             |           
  --             |daryUniq-|NewDefau-|             |           
  --  NewUniqueID|ueKey    |  ltInt  |NewField4    |NewField5  
  -- ------------|---------|---------|-------------|-----------
  --       1     |1        |    5    |John Smith   |Tennis     
  --       2     |2        |    5    |Joan Smith   |Football   
  --       3     |3        |    5    |James Smith  |Snooker    
  --       4     |NULL     |    5    |John Jones   |Badminton  
  --       5     |NULL     |    5    |Joan Jones   |Soccer     
  --       6     |NULL     |    5    |James Jones  |Pool       


-- After-the-fact key adding:

WITH NMAX(PK,NewKey) AS (
    SELECT MAX(NewUniqueID)+1, MAX(NewSecondaryUniqueKey)+1
      FROM New_Better_Table
     WHERE NewSecondaryUniqueKey IS NOT NULL
    UNION ALL
    SELECT PK+1, NewKey+1 FROM NMAX WHERE PK < 1000 -- however much needed
)
UPDATE New_Better_Table SET
  NewSecondaryUniqueKey = (SELECT NewKey FROM NMAX WHERE PK = NewUniqueID)
 WHERE NewSecondaryUniqueKey IS NULL
;


SELECT * FROM New_Better_Table;


  --             |NewSecondar-|NewDefau-|             |           
  --  NewUniqueID| yUniqueKey |  ltInt  |NewField4    |NewField5  
  -- ------------|------------|---------|-------------|-----------
  --       1     |      1     |    5    |John Smith   |Tennis     
  --       2     |      2     |    5    |Joan Smith   |Football   
  --       3     |      3     |    5    |James Smith  |Snooker    
  --       4     |      4     |    5    |John Jones   |Badminton  
  --       5     |      5     |    5    |Joan Jones   |Soccer     
  --       6     |      6     |    5    |James Jones  |Pool       

```


HTH! - Cheers,
Ryan