Search code examples
sqloracle-databaseplsqlsequences

Cycle Sequence for each value

I have such table:

           Table "test_seq"
 ------------------------------------------------
   Column   |          Type          | Modifiers
 ------------------------------------------------ 
 account_id | integer                | not null
 code       | integer                | not null
 data       | character varying(255) | 

 Indexes:
  "test_seq_pkey" PRIMARY KEY, btree (account_id, code)

Task: each 'account_id' can have 'code' with 1 to 3 range. If the next account_id code is 4, the data in row 1 rewrites and sequence begin in cycle with 1.

Example:

account_id  code       data
    1         1       'data 1'
    1         2       'data 2'
    1         3       'data 3'
    1         1       'data 4'  # first row destroyed

Can i solve this task with DB instruments only?

Solution

  • While i do not understand the purpose of this, you can achieve this using triggers and stored procedures. the stored procedure will be called instead of direct inserts (you can remove the permissions for direct inserts).