Resetting the count on a Primary Key at a certain record? Impossible?

Hello,
Several consecutive records have been deleted out of an MSDE database
and the program that uses the database doesn't like it. I'm curious
if it's possible to... say... reset the primary key count so that the
records all shift to fill the "dead space" left by the deleted
records.
I have the feeling that this may be humorous to some of you, but any
input (including "you can't do this. that's the point of a primary
key") is appreciated.
Thanks!
Matt
hi Matt,
Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
you can "reset" the identity value using a DBCC CHECKIDENT with the RESEED
option specified..
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Mon, 02 Jul 2007 10:51:23 -0700, Matt Brown - identify wrote:

>Hello,
>Several consecutive records have been deleted out of an MSDE database
>and the program that uses the database doesn't like it. I'm curious
>if it's possible to... say... reset the primary key count so that the
>records all shift to fill the "dead space" left by the deleted
>records.
>I have the feeling that this may be humorous to some of you, but any
>input (including "you can't do this. that's the point of a primary
>key") is appreciated.
Hi Matt,
You can't do this. That's the point of a primary key.
Seriously - a primary key is intended to identify an entity. Think of
what would happen if you would change your name. Or get a new SSN.
Note that I am not contradicting Andrea. DBCC CHECKIDENT WITH RESEED
will affect new IDENTITY values, not existing values. I think that you
were asking about the latter.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
ooopppsss...
Hugo is right, completely right... I did not read with enougth attention and
only saw the "identity" resetting requirement... I'm sorry..
I'm just curious why "..program that uses the database doesn't like it.." :D
Agreeing completely with Hugo about the "keys" immutability, you can perhaps
workaround that..
you could create a "temporary" table where you insert all the current valid
rows of your table, truncate the original table in order to empty it and to
reset the idientity table's value, and finally refill it with the "orphaned"
rows..
so you can write something like
INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
TRUNCATE TABLE dbo.YourTable;
INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
SELECT all_columns_but_the_identity_col
FROM tempTable
WHERE... ORDER BY...;
DROP TABLE tempTable;
but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
or then.. and this "workaround" does not work if you have declarative
referential integrity set on that table, if it's referenced by other rows in
other tables...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Jul 3, 6:34 am, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> Matt Brown - identify wrote:
>
>
> ooopppsss...
> Hugo is right, completely right... I did not read with enougth attention and
> only saw the "identity" resetting requirement... I'm sorry..
> I'm just curious why "..program that uses the database doesn't like it.." :D
> Agreeing completely with Hugo about the "keys" immutability, you can perhaps
> workaround that..
> you could create a "temporary" table where you insert all the current valid
> rows of your table, truncate the original table in order to empty it and to
> reset the idientity table's value, and finally refill it with the "orphaned"
> rows..
> so you can write something like
> INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
> TRUNCATE TABLE dbo.YourTable;
> INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
> SELECT all_columns_but_the_identity_col
> FROM tempTable
> WHERE... ORDER BY...;
> DROP TABLE tempTable;
> but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
> or then.. and this "workaround" does not work if you have declarative
> referential integrity set on that table, if it's referenced by other rows in
> other tables...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
I haven't checked this is a while. That's actually perfect. I can
remove some recordsets, then reset the identity key count with this
here DBCC CHECKIDENT WITH RESEED, then everything should progress as
normal.
Righto!
Thanks,
Matt

resetting master.mdf

Hey! I downloaded the sql engine and made a bunch of tables in visual basic. They were called new1, new2, new3, and new4. So I deleted new1.mdf, new1.ldf, new2.mdf, etc... but I notice I still can't recreate them. So I'm thinking there's remnants in master.mdf or something?? How would I go about getting rid of all the old tables, even ones whose names I've forgotten, so I can have a clean slate again? (btw I don't have sql server, just the desktop engine)mdf's are not tables..they are databases...

Did you DROP the databases?

If so they are gone....|||thanks for the quick reply. I didn't drop the databases. In the VB code all I did was 'create database... etc'. So, how would I drop all the databases, even if I don't remember the names of all of the ones that I created? Like is there a way to reset all that stuff so it will be like I just installed it?|||SELECT * FROM master..sysdatabases

Just don't touch master, msdb, model, pubs or northwind

And you probably should do some maintenance

Check out

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx|||thanks! I'll check that site out.

Resetting Key Column Numeration from SQL Tables

I used a lot of fictitious data in order to test the code of my web site. But I noticed that when I deleted all fictitious data from the tables, the primary key columns keep creating automatically numbers that are above the last one before all data was erased instead of starting again from number 1. How do I reset this automatic numeration in order to restart from 1 again ?

You have to use the DBCC Checkpoint command

Refer this: http://www.sqlteam.com/item.asp?ItemID=8003

|||Check out BOL for DBCC CHECKIDENT command.|||

Another way to do this is to use Truncate Table Command rather than delete.

This will reset identity.

resetting database

hi!
i'm new to sql server and enterprise manager and i accidentally deleted all data in a table. how do i recover them? the table (CaptureManager) is still there but empty. i have the following information:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaptureManager]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CaptureManager]
GO

CREATE TABLE [dbo].[CaptureManager] (
[ID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ext] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

The easiet way is to restore the database from backup and do INSERT INTO the old table which means everything remains the same you just return the deleted data. The Backup and restore Wizard lets you restore the database with any name you like. Hope this helps.|||unfortunately, i dont have a backup.|||If you buy some expensive log viewing software you may still have the original inserts logged. But really the chance of getting your data back is pretty minimal. I guess it's called learning by your mistakes, sorry :(
|||Try this link to test drive some tools. Hope this helps.
http://www.sql-server-performance.com/greg_robidoux_interview.asp

Reset identity column

A number of records were written to a table with an identity column that has an identity seed.
These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then remov
ed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
Thanks for any assistance on this.
Never mind, I have answered my own question: dbcc checkident is the solution...
Thanks
"TomT" wrote:

> A number of records were written to a table with an identity column that has an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then rem
oved. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
> Thanks for any assistance on this.

Reset identity column

A number of records were written to a table with an identity column that has
an identity seed.
These records had to be deleted, and I would like to begin the numeric seque
nce at a point as if the records had never been added. E.g., before the inco
rrect records were added, the value of this column was 2500. Two hundred rec
ords were added, then remov
ed. I would like the next value to be 2501, however new records are starting
at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the val
ue, or is there another way?
Thanks for any assistance on this.Never mind, I have answered my own question: dbcc checkident is the solution
...
Thanks
"TomT" wrote:

> A number of records were written to a table with an identity column that h
as an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at
a point as if the records had never been added. E.g., before the incorrect records
were added, the value of this column was 2500. Two hundred records were added, then
rem
oved. I would like the next value to be 2501, however new records are starting at 2701, afte
r the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the v
alue, or is there another way?
> Thanks for any assistance on this.

Reset identity column

A number of records were written to a table with an identity column that has an identity seed.
These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then removed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
Thanks for any assistance on this.Never mind, I have answered my own question: dbcc checkident is the solution...
Thanks
"TomT" wrote:
> A number of records were written to a table with an identity column that has an identity seed.
> These records had to be deleted, and I would like to begin the numeric sequence at a point as if the records had never been added. E.g., before the incorrect records were added, the value of this column was 2500. Two hundred records were added, then removed. I would like the next value to be 2501, however new records are starting at 2701, after the incorrect records were removed.
> Is DBCC CHECKIDENT the only way to correct this? Do I have to reseed the value, or is there another way?
> Thanks for any assistance on this.

Re-run snapshot?

I deleted the Indexes from several tables. Should I force a re-run of the
snapshot agent?
You should reinitialize if
1) you want the same indexes replicated to the subscriber and are
replicating unclustered indexes - make sure run a sp_refreshsubscriptions
2) you have evaluated the performance impact of your replication solution by
having additional indexes on your subscriber. Additional indexes will slow
inserts, updates, deletes
Merely re-running the snapshot may not do anything.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:u0VNsYHNFHA.2464@.TK2MSFTNGP10.phx.gbl...
> I deleted the Indexes from several tables. Should I force a re-run of the
> snapshot agent?
>
|||Thanks Hilary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OD18VcHNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> You should reinitialize if
> 1) you want the same indexes replicated to the subscriber and are
> replicating unclustered indexes - make sure run a sp_refreshsubscriptions
> 2) you have evaluated the performance impact of your replication solution
> by
> having additional indexes on your subscriber. Additional indexes will slow
> inserts, updates, deletes
> Merely re-running the snapshot may not do anything.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
> news:u0VNsYHNFHA.2464@.TK2MSFTNGP10.phx.gbl...
>

Representation for Deleted Entities: difficult question

Our customer (of our ecommerce system) wants to be able to preserve
deleted entities in the database so that they can do reporting,
auditing etc.

The system is quite complex where each end user can belong to multiple
institutional affiliations (which can purchase on behalf of the user).
The end user also has a rich trail of past transactions affiliations
etc. Thus in the schema each user entity is related to many others
which in turn relate to yet others and so on.

In the past when a user was deleted all of his complex relationships
were also deleted in a cascading fashion. But now the customer wants
us to add a "deleted" flag to each user so that a user is never
_really_ deleted but instead his "deleted" flag is set to true. The
system subsequently behaves as if the user did not exist but the
customer can still do reports on deleted users.

I pointed out that it is not as simple as that because the user entity
is related to many, many others so we would have to add this "deleted"
flag to every relationship and every other entity and thus have
"deleted" past purchases, "deleted" affiliations - a whole shadow
schema full of such ghost entities. This would overtime degrade
performance since now each query in the system has to add a clause:
"where deleted = 0".

I assume this is a standard problem since many organizations must have
this need of preserving deleted records (for legal or other reasons).
I tried to talk them into creating a simple audit file where all the
deletions will be recorded in XML but they were not too happy with
that.

Is there a more satisfying solution to this than have this "deleted"
flag?

Thanks for your help,

- robertYou didn't post database you're using.

In Oracle you could partition the main table (from where all
'cascaded' is coming from) into deleted/undeleted records. Put a view
on top of the table just as original table would look like with where
clause 'undeleted' and the undeleted partition would always be used.
Only certain versions of oracle allow rows to migrate from partition
to partition, u can look it up.

However, you would still suffer from 'performance' issues on the other
tables, if your data is truly huge.

Best solution would probably be to separate all deleted/undeleted data
into separate tables, and built a union view on top of them for
reporting purposes.

my2c
Someone might have a better idea.

On 13 May 2004 11:32:21 -0700, robertbrown1971@.yahoo.com (Robert
Brown) wrote:

>Our customer (of our ecommerce system) wants to be able to preserve
>deleted entities in the database so that they can do reporting,
>auditing etc.
>The system is quite complex where each end user can belong to multiple
>institutional affiliations (which can purchase on behalf of the user).
>The end user also has a rich trail of past transactions affiliations
>etc. Thus in the schema each user entity is related to many others
>which in turn relate to yet others and so on.
>In the past when a user was deleted all of his complex relationships
>were also deleted in a cascading fashion. But now the customer wants
>us to add a "deleted" flag to each user so that a user is never
>_really_ deleted but instead his "deleted" flag is set to true. The
>system subsequently behaves as if the user did not exist but the
>customer can still do reports on deleted users.
>I pointed out that it is not as simple as that because the user entity
>is related to many, many others so we would have to add this "deleted"
>flag to every relationship and every other entity and thus have
>"deleted" past purchases, "deleted" affiliations - a whole shadow
>schema full of such ghost entities. This would overtime degrade
>performance since now each query in the system has to add a clause:
>"where deleted = 0".
>I assume this is a standard problem since many organizations must have
>this need of preserving deleted records (for legal or other reasons).
>I tried to talk them into creating a simple audit file where all the
>deletions will be recorded in XML but they were not too happy with
>that.
>Is there a more satisfying solution to this than have this "deleted"
>flag?
>Thanks for your help,
>- robert

......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email|||"Robert Brown" <robertbrown1971@.yahoo.com> wrote in message
news:240a4d09.0405131032.6c2e9802@.posting.google.c om...
> I pointed out that it is not as simple as that because the user entity
> is related to many, many others so we would have to add this "deleted"
> flag to every relationship and every other entity and thus have
> "deleted" past purchases, "deleted" affiliations - a whole shadow
> schema full of such ghost entities. This would overtime degrade
> performance since now each query in the system has to add a clause:
> "where deleted = 0".

Blanket statements like this are rarely true.

> I assume this is a standard problem since many organizations must have
> this need of preserving deleted records (for legal or other reasons).
> I tried to talk them into creating a simple audit file where all the
> deletions will be recorded in XML but they were not too happy with
> that.

So you think file is better than DBMS?

> Is there a more satisfying solution to this than have this "deleted"
> flag?

Can I suggest that there is a modelling problem? I can imagine customer
having multiple timestamp columns, for example

table customer (
...
DOB DATE,
married DATE,
divorced DATE,
died DATE
)

but can't possibly see why you need "is_alive", "is_married" boolean
columns.|||andreyNSPAM@.bookexchange.net (NetComrade) wrote in message news:<40a3cbf2.428366208@.localhost>...

> You didn't post database you're using.

Thanks for your answer. This particular customer is using Oracle but
our software is supported on SQL server as well.

> In Oracle you could partition the main table (from where all
> 'cascaded' is coming from) into deleted/undeleted records. Put a view
> on top of the table just as original table would look like with where
> clause 'undeleted' and the undeleted partition would always be used.
> Only certain versions of oracle allow rows to migrate from partition
> to partition, u can look it up.
> However, you would still suffer from 'performance' issues on the other
> tables, if your data is truly huge.
> Best solution would probably be to separate all deleted/undeleted data
> into separate tables, and built a union view on top of them for
> reporting purposes.
> my2c
> Someone might have a better idea.
>
> On 13 May 2004 11:32:21 -0700, robertbrown1971@.yahoo.com (Robert
> Brown) wrote:
> >Our customer (of our ecommerce system) wants to be able to preserve
> >deleted entities in the database so that they can do reporting,
> >auditing etc.
> >The system is quite complex where each end user can belong to multiple
> >institutional affiliations (which can purchase on behalf of the user).
> >The end user also has a rich trail of past transactions affiliations
> >etc. Thus in the schema each user entity is related to many others
> >which in turn relate to yet others and so on.
> >In the past when a user was deleted all of his complex relationships
> >were also deleted in a cascading fashion. But now the customer wants
> >us to add a "deleted" flag to each user so that a user is never
> >_really_ deleted but instead his "deleted" flag is set to true. The
> >system subsequently behaves as if the user did not exist but the
> >customer can still do reports on deleted users.
> >I pointed out that it is not as simple as that because the user entity
> >is related to many, many others so we would have to add this "deleted"
> >flag to every relationship and every other entity and thus have
> >"deleted" past purchases, "deleted" affiliations - a whole shadow
> >schema full of such ghost entities. This would overtime degrade
> >performance since now each query in the system has to add a clause:
> >"where deleted = 0".
> >I assume this is a standard problem since many organizations must have
> >this need of preserving deleted records (for legal or other reasons).
> >I tried to talk them into creating a simple audit file where all the
> >deletions will be recorded in XML but they were not too happy with
> >that.
> >Is there a more satisfying solution to this than have this "deleted"
> >flag?
> >Thanks for your help,
> >- robert
> ......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email|||Note that SQL Server 2000 EE does support partitioned views.

On 14 May 2004 09:14:47 -0700, robertbrown1971@.yahoo.com (Robert Brown) wrote:

>andreyNSPAM@.bookexchange.net (NetComrade) wrote in message news:<40a3cbf2.428366208@.localhost>...
>> You didn't post database you're using.
>Thanks for your answer. This particular customer is using Oracle but
>our software is supported on SQL server as well.
>
>> In Oracle you could partition the main table (from where all
>> 'cascaded' is coming from) into deleted/undeleted records. Put a view
>> on top of the table just as original table would look like with where
>> clause 'undeleted' and the undeleted partition would always be used.
>> Only certain versions of oracle allow rows to migrate from partition
>> to partition, u can look it up.
>>
>> However, you would still suffer from 'performance' issues on the other
>> tables, if your data is truly huge.
>>
>> Best solution would probably be to separate all deleted/undeleted data
>> into separate tables, and built a union view on top of them for
>> reporting purposes.
>>
>> my2c
>> Someone might have a better idea.
>>
>>
>>
>> On 13 May 2004 11:32:21 -0700, robertbrown1971@.yahoo.com (Robert
>> Brown) wrote:
>>
>> >Our customer (of our ecommerce system) wants to be able to preserve
>> >deleted entities in the database so that they can do reporting,
>> >auditing etc.
>>> >The system is quite complex where each end user can belong to multiple
>> >institutional affiliations (which can purchase on behalf of the user).
>> >The end user also has a rich trail of past transactions affiliations
>> >etc. Thus in the schema each user entity is related to many others
>> >which in turn relate to yet others and so on.
>>> >In the past when a user was deleted all of his complex relationships
>> >were also deleted in a cascading fashion. But now the customer wants
>> >us to add a "deleted" flag to each user so that a user is never
>> >_really_ deleted but instead his "deleted" flag is set to true. The
>> >system subsequently behaves as if the user did not exist but the
>> >customer can still do reports on deleted users.
>>> >I pointed out that it is not as simple as that because the user entity
>> >is related to many, many others so we would have to add this "deleted"
>> >flag to every relationship and every other entity and thus have
>> >"deleted" past purchases, "deleted" affiliations - a whole shadow
>> >schema full of such ghost entities. This would overtime degrade
>> >performance since now each query in the system has to add a clause:
>> >"where deleted = 0".
>>> >I assume this is a standard problem since many organizations must have
>> >this need of preserving deleted records (for legal or other reasons).
>> >I tried to talk them into creating a simple audit file where all the
>> >deletions will be recorded in XML but they were not too happy with
>> >that.
>>> >Is there a more satisfying solution to this than have this "deleted"
>> >flag?
>>> >Thanks for your help,
>>> >- robert
>>
>> ......
>> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
>> remove NSPAM to email|||This solution is not specific to Oracle. Most DBMS support partitions and
views.

--
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike@.nospam!datamodel.org
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

"NetComrade" <andreyNSPAM@.bookexchange.net> wrote in message
news:40a3cbf2.428366208@.localhost...
> You didn't post database you're using.
> In Oracle you could partition the main table (from where all
> 'cascaded' is coming from) into deleted/undeleted records. Put a view
> on top of the table just as original table would look like with where
> clause 'undeleted' and the undeleted partition would always be used.
> Only certain versions of oracle allow rows to migrate from partition
> to partition, u can look it up.
> However, you would still suffer from 'performance' issues on the other
> tables, if your data is truly huge.
> Best solution would probably be to separate all deleted/undeleted data
> into separate tables, and built a union view on top of them for
> reporting purposes.
> my2c
> Someone might have a better idea.
>
> On 13 May 2004 11:32:21 -0700, robertbrown1971@.yahoo.com (Robert
> Brown) wrote:
> >Our customer (of our ecommerce system) wants to be able to preserve
> >deleted entities in the database so that they can do reporting,
> >auditing etc.
> >The system is quite complex where each end user can belong to multiple
> >institutional affiliations (which can purchase on behalf of the user).
> >The end user also has a rich trail of past transactions affiliations
> >etc. Thus in the schema each user entity is related to many others
> >which in turn relate to yet others and so on.
> >In the past when a user was deleted all of his complex relationships
> >were also deleted in a cascading fashion. But now the customer wants
> >us to add a "deleted" flag to each user so that a user is never
> >_really_ deleted but instead his "deleted" flag is set to true. The
> >system subsequently behaves as if the user did not exist but the
> >customer can still do reports on deleted users.
> >I pointed out that it is not as simple as that because the user entity
> >is related to many, many others so we would have to add this "deleted"
> >flag to every relationship and every other entity and thus have
> >"deleted" past purchases, "deleted" affiliations - a whole shadow
> >schema full of such ghost entities. This would overtime degrade
> >performance since now each query in the system has to add a clause:
> >"where deleted = 0".
> >I assume this is a standard problem since many organizations must have
> >this need of preserving deleted records (for legal or other reasons).
> >I tried to talk them into creating a simple audit file where all the
> >deletions will be recorded in XML but they were not too happy with
> >that.
> >Is there a more satisfying solution to this than have this "deleted"
> >flag?
> >Thanks for your help,
> >- robert
> ......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email|||As Leandro and Mikito point out, you have flaws in your design.
First, deleting the user entity is the only thing that is logically deleted,
but rather than make it an indicator, use a date, as in DELETE_DATE as
nullable. Non-null entities are to be ignored.
In addition, all relationships to the user entity should be evaluated to
determine if they need to be sensitive to the user entity's status. Some
may, and others may not. This is a business question, not a technical
question. For instance, if there are invoices and inventory tables linked
in some way to the user entity, would you really want to *not* display that
information just because an associated user entity had been deleted?

I'd really need to see your design and talk to your business to know exactly
what impact this design change would have on your database.

--
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike@.nospam!datamodel.org
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

"Robert Brown" <robertbrown1971@.yahoo.com> wrote in message
news:240a4d09.0405131032.6c2e9802@.posting.google.c om...
> Our customer (of our ecommerce system) wants to be able to preserve
> deleted entities in the database so that they can do reporting,
> auditing etc.
> The system is quite complex where each end user can belong to multiple
> institutional affiliations (which can purchase on behalf of the user).
> The end user also has a rich trail of past transactions affiliations
> etc. Thus in the schema each user entity is related to many others
> which in turn relate to yet others and so on.
> In the past when a user was deleted all of his complex relationships
> were also deleted in a cascading fashion. But now the customer wants
> us to add a "deleted" flag to each user so that a user is never
> _really_ deleted but instead his "deleted" flag is set to true. The
> system subsequently behaves as if the user did not exist but the
> customer can still do reports on deleted users.
> I pointed out that it is not as simple as that because the user entity
> is related to many, many others so we would have to add this "deleted"
> flag to every relationship and every other entity and thus have
> "deleted" past purchases, "deleted" affiliations - a whole shadow
> schema full of such ghost entities. This would overtime degrade
> performance since now each query in the system has to add a clause:
> "where deleted = 0".
> I assume this is a standard problem since many organizations must have
> this need of preserving deleted records (for legal or other reasons).
> I tried to talk them into creating a simple audit file where all the
> deletions will be recorded in XML but they were not too happy with
> that.
> Is there a more satisfying solution to this than have this "deleted"
> flag?
> Thanks for your help,
> - robert|||robertbrown1971@.yahoo.com (Robert Brown) wrote:
> Our customer (of our ecommerce system) wants to be able to preserve
> deleted entities in the database so that they can do reporting,
> auditing etc.
> The system is quite complex where each end user can belong to multiple
> institutional affiliations (which can purchase on behalf of the user).
> The end user also has a rich trail of past transactions affiliations
> etc. Thus in the schema each user entity is related to many others
> which in turn relate to yet others and so on.
> In the past when a user was deleted all of his complex relationships
> were also deleted in a cascading fashion.

Users are not deleted. They may die, they may be incarcerated, they may
be fired, but they cannot be deleted. Only data is deleted.

> But now the customer wants
> us to add a "deleted" flag to each user so that a user is never
> _really_ deleted but instead his "deleted" flag is set to true. The
> system subsequently behaves as if the user did not exist but the
> customer can still do reports on deleted users.
> I pointed out that it is not as simple as that because the user entity
> is related to many, many others so we would have to add this "deleted"
> flag to every relationship and every other entity and thus have
> "deleted" past purchases,

If I am hit by a bus tomorrow, will the office furniture I ordered (but
which is owned by the company) disappear? Will the patents I've generated
for the company no longer be valid?

> "deleted" affiliations - a whole shadow
> schema full of such ghost entities.

Why would each of these need it's own deleted flag? If they are being
joined against the user table, then they can just rely on the user table to
provide the necessary logic. If they are not being joined against the user
table, then why would you want them to be excluded based on a condition of
a user?

> This would overtime degrade
> performance since now each query in the system has to add a clause:
> "where deleted = 0".

Do they want the reporting to go back indefinately, or would they like
the data to be "really" deleted after a certain time of "virtual" deletion?

> I assume this is a standard problem since many organizations must have
> this need of preserving deleted records (for legal or other reasons).

It is a standard problem, but there is not a standard solution.

Xho

--
------- http://NewsReader.Com/ -------
Usenet Newsgroup Service $9.95/Month 30GB|||>It is a standard problem, but there is not a standard solution.
>Xho

Put a bit column in there and flag it if this record is "deleted".
Or write a trigger and dump the deleted records into an archive
table.

Randy
http://members.aol.com/rsmeiner|||Robert Brown (robertbrown1971@.yahoo.com) writes:
> Our customer (of our ecommerce system) wants to be able to preserve
> deleted entities in the database so that they can do reporting,
> auditing etc.
> The system is quite complex where each end user can belong to multiple
> institutional affiliations (which can purchase on behalf of the user).
> The end user also has a rich trail of past transactions affiliations
> etc. Thus in the schema each user entity is related to many others
> which in turn relate to yet others and so on.
> In the past when a user was deleted all of his complex relationships
> were also deleted in a cascading fashion. But now the customer wants
> us to add a "deleted" flag to each user so that a user is never
> _really_ deleted but instead his "deleted" flag is set to true. The
> system subsequently behaves as if the user did not exist but the
> customer can still do reports on deleted users.
> I pointed out that it is not as simple as that because the user entity
> is related to many, many others so we would have to add this "deleted"
> flag to every relationship and every other entity and thus have
> "deleted" past purchases, "deleted" affiliations - a whole shadow
> schema full of such ghost entities. This would overtime degrade
> performance since now each query in the system has to add a clause:
> "where deleted = 0".

Indeed, this is quite a big change if this was not in the system from
the beginning.

In our system, many items are not deletable, because even if an item
goes away, there might still be plenty of references to it. So in these
tables - accounts, customers, instruments, currencies, to name a few - there
is a deregdate column. This column serves the double purpose of telling
us if the item is still active, and if it is not, when the entity was
deregistered. And, yes it happens that deregistered entities are revived
too!

A non-trivial issue here is to know when a deregistered item should be
included and when it should not. If you are producing a list of last
month's tranactions, it obviously should. But if you are populating a
list of available products to order, deregistered products should not be
included. So when you introduce this concept in your system, you have
a lot to write in your functional specification too.

Another issue which becomes complicated, is referential integrity.
accounts.accresponsible may refer to the users table, but if the account
is active, the user must be too. Currently we do this in triggers, which
is a bit complex, and more difficult than foreign-key constraints. One
thought I've been playing with is to have tables like active_accounts,
active_users etc. This would not be the partition suggested by others,
since active_accounts would only hold the account number, and foreign
keys to other deregisterable items. Thus, active_accounts.accresponsible
would refer to active_users.userid. The full data would still be in
acconts and users, for both active and deregistered items.

I have never considered the performance cost for "AND deregdate IS NULL",
but I would suggest that if you need to access that column, you probably
already access some column which is not in any index, so there is
already a bookmark lookup, so I would not expect any particular penalty.
(This applies to MS SQL Server. Not that I really expect Oracle to be
different, but I don't know Oracle.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp