CODEWITHSUNDEEP
mysqldatabasedatabase-design
Harjeet Jadeja
Harjeet Jadeja

Reputation: 1604

how generate serial number in mysql database?

I have table like this:

table1:

sr no. | id | name

id is the primary key here. I want sr no. to be cyclic.
What attributes i should give to sr no. field?

For example I have 5 records with sr no 1,2,3,4,5 and id 1,2,3,4,5 and i delete 3rd record then id will be 1,2,4,5 and sr no. will be 1,2,3,4 sr no should not lost its contentiousness.

Upvotes: 1

Views: 5918

Answers (3)

Harjeet Jadeja
Harjeet Jadeja

Reputation: 1604

SELECT  @a:=@a+1 serial_number, 
        name  FROM    table1,
        (SELECT @a:= 0) AS a;

Upvotes: 4

fancyPants
fancyPants

Reputation: 51908

id is already your primary key, so what do you need sr no. for? Would suggest to forget about it.

If you really need it, see my answer here for the several reasons why this is a really bad idea. The most important reason:

There are reasons why MySQL doesn't automatically decrease the autoincrement value when you delete a row. Those reasons are

  • danger of broken data integrity (imagine multiple users perform deletes or inserts...doubled entries may occur or worse)
  • errors may occur when you use master slave replication or transactions
  • and so on ...

No need to worry about gaps.

To answer your question how to do it, just add auto_increment in the column definition and include it in the primary key.

Upvotes: 1

Minesh
Minesh

Reputation: 2302

I do not know about the real purpose of sr no.

But you can create Trigger on DELETE operation for this table, now you can update the records which are greater than current ID so that sr no is decremented by 1.

e.g. you can write below SQL in Trigger,

UPDATE table1 SET sr_no = sr_no -1 WHERE id > XYZ;

Upvotes: 1

Related Questions