Re: Snapshot Relational Data into New Tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Raterus (moc.liamtoh_at_suretar.reverse)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 15:10:33 -0400

I understand what you are saying, and I believe this will work, but I'm not sure where I can get a natural key out of my present data. Are there situations where natural keys are impossible because you simply don't have enough unique data there to create one? Creating them on SSN's and department names are one thing, but what if I told you I only had one word of a person's name to go on?

Let's revisit my situation. I have two big chunks of data pointing relating to each other (The data I'm searching for, and the data I'm searching against) This is a blacklist application that alerts me if someone is in our customer database who we shouldn't be dealing with.. How my process is running, it breaks down names into individual words, and searches for this one word within another full-name. If there is a match, I add this match to a new table, which has an identity primary key, and the only other fields are foreign integer keys. How could you get a natural key from that?

Thanks for your help on this, and excuse me for my ignorance!
--Michael

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:DC0241B1-E9D4-462E-A244-9E046A033B68@microsoft.com...
> Read the second sentence of my previous post again :) IDENTITY is a
> *surrogate* key only. You must also declare the natural key columns. This is
> fundamental design stuff.
>
> Here's an example of my own, which might make it clearer. The natural keys
> are Dept_Name and SSN in this case.
>
> CREATE TABLE Departments (dept_id INTEGER IDENTITY PRIMARY KEY, dept_name
> VARCHAR(30) NOT NULL UNIQUE)
>
> CREATE TABLE Employees (employee_id INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL UNIQUE, employee_name VARCHAR(30) NOT NULL, dept_id INTEGER
> NOT NULL REFERENCES Departments (dept_id))
>
> CREATE TABLE Departments_Archived (dept_id INTEGER IDENTITY PRIMARY KEY,
> dept_name VARCHAR(30) NOT NULL UNIQUE)
>
> CREATE TABLE Employees_Archived (employee_id INTEGER IDENTITY PRIMARY KEY,
> ssn CHAR(10) NOT NULL UNIQUE, employee_name VARCHAR(30) NOT NULL, dept_id
> INTEGER NOT NULL REFERENCES Departments_Archived (dept_id))
>
> Here's how to archive the data, while preserving the surrogate key -based
> relationship.
>
> INSERT INTO Departments_Archived (dept_name)
> SELECT D.dept_name
> FROM Departments AS D
> LEFT JOIN Departments_Archived AS N
> ON D.dept_name = N.dept_name
> WHERE N.dept_name IS NULL
>
> INSERT INTO Employees_Archived (ssn, employee_name, dept_id)
> SELECT E1.ssn, E1.employee_name, D2.dept_id
> FROM Employees AS E1
> JOIN Departments AS D1
> ON E1.dept_id = D1.dept_id
> JOIN Departments_Archived AS D2
> ON D1.dept_name = D2.dept_name
> LEFT JOIN Employees_Archived AS E2
> ON E1.ssn = E2.ssn
> WHERE E2.employee_id IS NULL
>
> Without the keys I don't think you'll find a reliable set-based solution.
> You'll have to iterate through the parent table doing the inserts one by one,
> each time inserting the child rows using SCOPE_IDENTITY() - it really is that
> bad! And of course that would just be a kludge rather than a fix the real
> problem...
>
> --
> David Portas
> SQL Server MVP
> --
>

Relevant Pages

  • Re: Use IDs or strings?
    ...     If the answer to either of these is yes, ... internal IDs is not as automatic as the above requests indicate. ... If the ONLY purpose behind ID's is to join  foreign keys with the keys they ... SSN to identify Job seekers  (with dummy SSNs for job seekers with no SSN or ...
    (comp.databases)
  • Re: SSN encryption
    ... If the SSN can be ... then the original records including the SSN can be ... unique random key and store that mapping table safely with the original ... to map existing SSNs to the proper keys, and create new keys for new ...
    (sci.crypt)
  • Re: Choosing a surrogate key vs. a composite key - "holy war" issue or clear best practice?
    ... vs. natural keys. ... There are simple keys and composite keys (also known as complex keys ... Well the SSN example again brings up problems. ... Here is another fact involving Social Security Numbers that many ...
    (comp.databases.oracle.server)
  • Re: Inserting a new PK into an existing table
    ... natural keys). ... @home.com is bogus. ... But you might want to do a two-field PK with SSN and DOB. ...
    (comp.databases.oracle.misc)
  • Re: Totally Confused
    ... keys in the juction table (emp. ... Employees table, but data remains in tblKeysEmployees. ... The juction table has 2 primary keys: ...
    (microsoft.public.access.gettingstarted)