Reply-To: "Pablo Sanchez" <pablo@dev.null>
From: "Pablo Sanchez" <pablo@dev.null>
Newsgroups: comp.databases.oracle.misc,comp.databases.theory,comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming
References: <c1ec9b8f.0205151525.6dce24d2@posting.google.com> <eiWjfwR$BHA.2384@tkmsftngp02> <un0uz9tzv.fsf@rcn.com> <ac2kdj$lvg6t$1@ID-135366.news.dfncis.de> <u8z6ilw51.fsf@grossprofit.com> <e51b160.0205171113.6ba24063@posting.google.com> <3ce567e8_3@news.teranews.com> <MPG.174f38f14698dd6898970d@news.easynews.com> <3CE583F3.7F5D20D5@exesolutions.com>
Subject: Re: Normalization, Natural Keys, Surrogate Keys
Date: Fri, 17 May 2002 21:11:15 -0600
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <3ce5c46b$1_15@news.teranews.com>
X-Abuse-Report: Send abuse reports to abuse@teranews.com
Organization: http://www.TeraNews.com - FREE NNTP Access
Path: news.easynews.com!easynews!peer1-sjc1.usenetserver.com!usenetserver.com!iad-peer.news.verio.net!news.verio.net!news.maxwell.syr.edu!news-xfer.siscom.net!newsfeed-east.nntpserver.com!nntpserver.com!reseller.nntpserver.com
Xref: easynews comp.databases.oracle.misc:81800 comp.databases.theory:20836 comp.databases.ms-sqlserver:72122 microsoft.public.sqlserver.programming:238793
X-Received-Date: Fri, 17 May 2002 20:09:21 MST (news.easynews.com)


"Daniel Morgan" <dmorgan@exesolutions.com> wrote in message
news:3CE583F3.7F5D20D5@exesolutions.com...
> Paul Tiseo wrote:
>
> The reason some people go ballistic is as follows:
>
> CREATE TABLE employees (
>    surrogate_key   NUMBER,
>    ssn                    VARCHAR2(11));
>
> ALTER TABLE employees
> ADD CONSTRAINT pk_employees PRIMARY KEY (surrogate_key);
>
> CREATE SEQUENCE seq_surrogate_key START WITH 1;

You forgot:

CREATE UNIQUE INDEX ssn ON employee (ssn);

(btw, you should use singular rather than plural for table names.
<g>)

> INSERT INTO employees
> VALUES
> (seq_surrogate_key.NEXTVAL '555-55-5555');
>
> INSERT INTO employees
> VALUES
> (seq_surrogate_key.NEXTVAL '555-55-5555');
>
> COMMIT;
>
> It happens all the time.

Clearly.
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts