I occasionally use PKID: 0 inside tables to indicate a system record, one not to be displayed or tampered with by anyone.
Problem is, most tables are auto-incrementing so programatically when I set the PKID as ZERO when creating the record, it always increments anyways. I have to UPDATE the table to a PKID of zero...
Other than this, are there any side effects of reserving the PKID: ZERO for internal system use, in any RDBMS???
In case your wondering, it's just easier to use PKID ZERO than introduce yet another field into the mix to indicate system use only. Although I do use bit fields I prefer this ZERO PKID approach for truely system specific data.
Opinions or experiences???
PKID = ZERO - problems?
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: PKID = ZERO - problems?
That is the only thing that sounds problematic. I typically only select by the Primary Key when I want to fetch one specific record as it is unique. Otherwise I would use other column's values to select multiple records. So it seem to me that doing "WHERE id>0" is not a very good practice. I am not sure what a "system record" is in database terms. It sounds like a convention. There are probably better ways to have records that are "not to be displayed or tampered with by anyone."Hockey wrote:I occasionally use PKID: 0 inside tables to indicate a system record, one not to be displayed or tampered with by anyone.
(#10850)
- dreamscape
- Forum Commoner
- Posts: 87
- Joined: Wed Jun 08, 2005 10:06 am
- Contact:
Re: PKID = ZERO - problems?
The main issue I see is with the design. Primary keys are unique by definition (and I believe most RDBMS will enforce this). So that would only allow you to have one system record per table.
So, unless you only need one system record per table, it's a bad idea for sure.
So, unless you only need one system record per table, it's a bad idea for sure.