2 Replies - 2422 Views - Last Post: 09 February 2012 - 07:15 PM

#1 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Creating a primary key with a sequence that increments

Posted 08 February 2012 - 09:01 PM

Scenario:

I need to format a sequence such that each new record increments by 1. However, the data type is char(10) and begins with a 'P' and how ever many spaces are left are to be filled with zeros. It must always take up 10 spaces.

I have created a sequence that begins with 1 and increments by 1, and just added 'P' concat'd with zeros in the insert statements. I do not like this approach.

Does anyone have any suggestions?

Is This A Good Question/Topic? 0
  • +

Replies To: Creating a primary key with a sequence that increments

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Creating a primary key with a sequence that increments

Posted 08 February 2012 - 11:47 PM

Does the "P" have to be stored with the data? If it is merely for presentation, it would make much more sense to store the PK as a normal incrementing integer value, and then cancat the P onto that on the way out.

For instance, I'd imagine something like this would work, assuming the PK is a simple integer value.
SELECT 'P' || TO_CHAR(the_pk, 'FM000000000')
FROM the_table;



You could even make a view to make that easier, without having to actually store the "P".
Was This Post Helpful? 2
  • +
  • -

#3 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: Creating a primary key with a sequence that increments

Posted 09 February 2012 - 07:15 PM

Yes I agree with you. It's actually an assignment my db class and the teacher was specific about making it a char(10). I get the feeling this is probably not a good practice for creating a PK. I think I'm going with this solution. Thanks for the reply.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1