Home » SQL & PL/SQL » SQL & PL/SQL » SEQUENCEs continuous
SEQUENCEs continuous [message #303417] Fri, 29 February 2008 05:26 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hello,
I would like to ask you about continuous sequences in the Oracle.
Let's go with a "schoolar example": Assume that I have two ugly tables: students and groups:

select * from students

StudentId,FirstName,LastName
1,James,Brown
2,John,Smith

select * from groups

GroupId,StudentId
1,1
1,2
2,2

So that James Brown and John Smith are both in group 1, however in the group 2 there is only John Smith. Yes - I know that this example is nasty.

New students are added like that:
insert into students (StudentId,FirstName,LastName) values (seq_studentid.NextVal,'Donnald','Duck')


select * from students

StudentId,FirstName,LastName
1,James,Brown
2,John,Smith
3,Donnald,Duck

Now I want to remove John Smith from the list of students:
delete from students where StudentId = 2
delete from groups where StudentId = 2


And I would like to add another student but it should get StudentId=2, not 4.

How to implement such a mechanism?
I know that the best way would be to just modify students to have a column like "valid" and to assign a new StudentId for new students, but lets assume that this is not what we want.
Re: SEQUENCEs continuous [message #303423 is a reply to message #303417] Fri, 29 February 2008 06:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
First let me say that this is a very bad idea design-wise.

You CAN find the lowest free ID which is not in the students table with something like

SELECT Min(rn) FROM (
   SELECT ROWNUM rn FROM all_objects  -- or any other row generator
)  WHERE rn NOT IN (SELECT StudentId FROM students)


But it will definitely not scale well into a any multi-user system, and the probability is very high that one day end up with some data that is left over from another student attached to a new one.

So unless this is an existing system, and you have run out of IDs or something and HAVE to do an workaround until it is fixed properly design-wise, I would suggest to not take that approach.
Re: SEQUENCEs continuous [message #303424 is a reply to message #303417] Fri, 29 February 2008 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle does not give you a built-in for that.
The question is why do you need to reuse the numbers?

Regards
Michel
Re: SEQUENCEs continuous [message #303448 is a reply to message #303417] Fri, 29 February 2008 06:42 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
There is some existing database. Tables without keys nor indexes, with hundreds of user types with meaningless names, some procedures, several sequences that were designed but are not in use.
As a result there are existing entries with IDs that were manually added. It is being used by some code in .NET and some in PL/SQL and some in PERL. I cannot tell what is inside it and there is a risk that if some "leaps" are present in the numbering - then some parts of code can stop processing after "leap" is found (in other words: there is a risk that some code could process ordered rows until there is no row with next number).
However I am going to ask about this - it seems that the existing implementation is not consistent with initial database design (which is not so great as well).
Previous Topic: handling leading zeros in a csv file
Next Topic: Oracle Problem
Goto Forum:
  


Current Time: Sat Dec 03 21:59:37 CST 2016

Total time taken to generate the page: 0.09265 seconds