Re: How can you create a field that is concatenated?

On Dec 18, 2:14 pm, "Rick Brandt" <rickbran...@xxxxxxxxxxx> wrote:
Jamie, the topic "should AutoNumbers be exposed to users?" and "do AutoNumbers
make good primary keys?" are completely different!

Agreed.

I consider all these to be distinct topics:

1) keys in relational theory,
2) primary keys in relational theory (deprecated),
3) artificial keys in databases,
4) surrogate keys in databases,
5) PRIMARY KEY in the SQL language,
6) PRIMARY KEY in the Jet product,
7) Autonumber in the Jet product.

My point was nothing to do with 2, 4, 5 nor 6; rather, it was whether
Jet Autonumbers (7) are suitable for use as artificial keys (1 and 3)?
I say no.

I would expect
any competent developer to do all of the other proper stuff like enforcing
uniqueness on other data fields so as to prevent duplication of data.

The point (John Vinson's) I was responding to was about a scenario
where all the available attributes do not provide a unique key...

Everything you say about
"keys" is true of AutoNumbers whether the user sees them or not.

....so if in the abovementioned scenario one is using an AutoNumber as
an artificial key then one *must* expose it, otherwise there's no
point in having it. Further, I don't think any flavour of Autonumber
is suitable for the human eye. I've no argument against an Autonumber
which nobody sees (e.g. Codd's description of a surrogate key i.e. not
visible even to database developers/DBAs).

Rather then adhering to some strict dogma one should evaluate each
situation.

Are you saying that we shouldn't have "rules of thumb" because there
might - shock horror - be exceptions to the rule?

The following are all true...

Oh yeah...?

One should not use a byte if one needs values greater than 255.

What about using the negative range of byte for extra 'headroom'?
Anyhow, I'd always use INTEGER (Long) in place of byte because it is
more portable.

One should not use an integer if one needs fractional values.

Some people use an integer type to scale their own decimal (e.g.
storing integer seconds rather than decimal hours). Me, I'd use
DECIMAL which, again, ports well. Some people say one should use
CURRENCY for up to four decimal places otherwise a floating point type
(usually FLOAT) because DECIMAL is somehow evil whereas I prefer the
fixed point DECIMAL wherever possible (as does Jet natively <g>) and
other people say other things but then that's the nature of
discussion...

One should not use an AutoNumber if they don't want gaps.

Isn't that a bit like saying, "One should not use an AutoNumber if
they don't want duplicate values" because there is nothing inherent in
Jet's AutoNumber functionality that guarantees uniqueness? If the
following can make an AutoNumber suitable if they don't want
duplicates...

CREATE TABLE Test (
ID IDENTITY(1, 1) NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL
);

....then it would follow that something like this would make AutoNumber
suitable if they don't want gaps...

CREATE TABLE Test (
ID IDENTITY(1, 1) NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL,
CONSTRAINT test__ID__no_gaps
CHECK (NOT EXISTS (
SELECT *
FROM Sequence AS S1
WHERE S1.Seq BETWEEN (SELECT MIN(T2.ID) FROM Test AS T2)
AND (SELECT MAX(T2.ID) FROM Test AS T2)
AND NOT EXISTS (
SELECT *
FROM Test AS T1
WHERE S1.Seq = T1.ID
)))
);

....where Sequence is a table of integers (seq), being a 'standard
issue' auxiliary table.

Perhaps a better assertion would have been, "One should not use an
incremental AutoNumber if they want to be able to UPDATE its values."

Jamie.

--


.

Relevant Pages

  • Re: Autonum field for Relationships and Replication
    ... restrict entries in candidate keys that could have been the PK?" ... autonumber field and a third with the autonumber foreign fields from the ... > CandidateID sequential in the replicated environment. ... > Replica B. Therefore creating duplicates. ...
    (microsoft.public.access.replication)
  • Re: Data Primary key vs. Artificial (Autonumber) primary key
    ... Presumably, if there are candidate keys available, they are what the user ... sequence by its primary key whenever the database is compacted. ... the clustering has no impact ... The main issue I have with the 'Autonumber PK' movement is that the ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Restart Autonumber
    ... I maintained then that in all cases arbitrary keys ... I use Autonumber values as ... > have a primary key unless you use multiple key fields. ...
    (microsoft.public.access.formscoding)
  • Re: Restart Autonumber
    ... I maintained then that in all cases arbitrary keys ... I use Autonumber values as ... > have a primary key unless you use multiple key fields. ...
    (microsoft.public.access.gettingstarted)
  • Re: Simple database layout - Am I on the right track?
    ... someone chose to use an Autonumber as an identifier and the bookkeeper, ... to the argument over whether an Autonumber surrogate key is "better" than ... the multi-part composite keys will be slower. ...
    (comp.databases.ms-access)
Quantcast