• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to set the limit for primary keys

 
chetan dhumane
Ranch Hand
Posts: 641
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to set the limit for the primary keys.
I want my records to be added from 1 to 50 only.
If I delete any record for eg. say 2.The next record should take the primary key as 2. Not 51.

Thanks in advance.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why does your primary key need business meaning? Any distinct, non-null value uniquely identifies a record and this is the only purpose of a primary key. If you are sure of this requirement you'll need to assign PK values manually (i.e. you can't rely on any database mechanism).

I can't think of a database constraint you could use to force only 50 records in a table. The best you could probably manage is a before insert trigger to raise an error if the count == 50.
 
chetan dhumane
Ranch Hand
Posts: 641
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah I can use the trigger.
But I want my record count of 50.
But I dont want to exceed My PK count above 50.(If in case we delete some records and if we add afterwards).

For example.
1 2 3 5
If I try to insert in this table my PK should be 4(i.e. the vacant key should be taken as next PK)

Regards ,
Chetan
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is better to define a check constraint instead of the trigger, the check constraint usually is faster than trigger.
On Oracle it can be done in this way:


It should be possible on others databases too.
 
chetan dhumane
Ranch Hand
Posts: 641
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah that is fine.

But will it work for autoincrement or not ?
If I set my PK for autoincrement .

I want this solution for Sqlite.
 
chetan dhumane
Ranch Hand
Posts: 641
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ireneusz Kordal wrote:It is better to define a check constraint instead of the trigger, the check constraint usually is faster than trigger.
On Oracle it can be done in this way:


It should be possible on others databases too.

Thanks for the solution.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic