How to add 'sequence'column to make a PK in table(Oracle and MySQL)-CodePudding

I have a question about adding a sequence column in the table I have.

The table looks like this.

-SQL(MySQL) :

SELECT A.CI_NUM, 
       A.DL_DT, 
       A.DL_PRC
FROM (SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 700 AS DL_PRC
      UNION ALL
      SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 500 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 600 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 600 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 350 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 400 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 300 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 350 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 200 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 700 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 650 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 400 AS DL_PRC
) A;

-result :

In this table, I want to add a sequence column to make a PK for this table.

The table that I want looks like this.

I want to add SQ column using ALTER TABLE and want to apply this in Oracle table and MySQL table.

If you answer this question, I really appreciate that.

CodePudding user response：

For Oracle 12c and above you may use generated ... as identity to create an autoincrement column. Then make it a primary key, because Oracle throws an error if you do this in single operation.

create table t(val int)

insert into t values(0)

alter table t add (
  id int generated by default as identity
)

alter table t add (
  constraint t_pk primary key (id)
)

select *
from t

VAL | ID
--: | -:
  0 |  1

db<>fiddle here

For MySQL it can be done in one step, because it forces you to declare it as a pk:

create table t(val int)

insert into t values(0)

alter table t add (
  id int auto_increment primary key
)

select *
from t

val | id
--: | -:
  0 |  1

db<>fiddle here

CodePudding user response：

Hi you can get the SQ value using window function, please see sql bellow:

SELECT A.CI_NUM, 
   A.DL_DT,
   row_number() over (partition by A.CI_NUM,A.DL_DT) AS SQ,
   A.DL_PRC
FROM (SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 700 AS DL_PRC
  UNION ALL
  SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 500 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 600 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 600 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 350 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 400 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 300 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 350 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 200 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 700 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 650 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 400 AS DL_PRC
) A;

If you have that table in your database you first need to add SQ column with some default value then use the sql above and write a cte that will be used for updating the original table, then you can add a composite PK on the table like this:

ALTER TABLE MyTble
   ADD CONSTRAINT PK_MyTable PRIMARY KEY(CI_NUM,DL_DT,SQ);

going forward you also will need trigger or somehow to get the next value in every group(CI_NUM,DL_DT) and use that as value for SQ in your insert statement.

Hope I give you an idea how to solve your problem.