• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

How to set the limit for primary keys

 
Ranch Hand
Posts: 643
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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE 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: 643
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
 
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: 643
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: 643
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.
 
We've gotta get close enough to that helmet to pull the choke on it's engine and flood his mind! Or, we could just read this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic