SEO

We are converting a legacy visual foxpro system to use a SQL back-end.

A number of (existing DBF) tables currently have a zero-filled primary
key eg. '000255' which is just an auto-incrementing key - but always
stored as a char field with leading zeros.

For backward compatibility we are considering retaining this primary
key and using an identity field to auto-generate the next value, then
convert the new identity value into the new primary key. So if
identity is 256 then the key field will be assigned '000256'.

Now the problem with this is that the primary key must be non-null and
unique so must be given a value in the INSERT statement. But, the
identity value isn't available (I presume) until after the INSERT
statement has executed.

Is this a "don't go there" kind of problem?

Thanks
Andrew Grandison
SA Department of Health
Adelaide, South Australia

> Now the problem with this is that the primary key must be non-null and
> unique so must be given a value in the INSERT statement. But, the
> identity value isn't available (I presume) until after the INSERT
> statement has executed.

That's correct, you don't know the IDENTITY value until after the rows are
inserted. It shouldn't be a problem. Leave the IDENTITY column as a numeric
and just format it with leading zeros in your application, in a view or when
you query the table.

In SQL an IDENTITY column should be used only as a *surrogate* key. You
should ensure that you declare the *natural* key of the table as unique and
non-NULL as well. As I expect you already know, the design considerations in
SQL are quite different to those in Fox and you should review your DB schema
verify that you have the appropriate keys in place and that your tables are
correctly normalized.

--
David Portas
SQL Server MVP
--

>> We are converting a legacy visual foxpro system to use a SQL
back-end. A number of (existing DBF) tables currently have a
zero-filled primary key eg. '000255' which is just an
auto-incrementing key <<

I am sorry that your original database is screwed so badly. Find the
original programmer who did not build a proper key in his design and
kill him. This will improve everyone's data quality.

>> .. stored as a char field with leading zeros. <<

If you don't know that a column is **nothing whatsoever** like a
field, you have a lot more problems.

>> For backward compatibility we are considering retaining this
primary key and using an identity field [sic] to auto-generate [sic]
the next value, then convert the new identity value into the new
primary key. <<

Why not fix the mess you inherited now instead propagating errors into
the future?

>> Now the problem with this is that the primary key must be non-null
and
unique .. <<

That is PART of the definition of a key, yes, ...

>> so must be given a value in the INSERT statement. <<

NO!! The key must exist in the entity in the reality which you are
modeling in the RDBMS. It is not part of the hardware at storage
time. How do you plan on verifying it against the real world??

>> Is this a "don't go there" kind of problem? <<

You can kludge your existing system only if you care about not maiming
or killing people.

I do a fair amount of free work for charity groups. Some volunteer
designed a database improperly for a group that sends medical supplies
to the third world. The shipments have to be as small and exact as
possible; running stuff across a battle zone does not allow for extra
size or weight.

He had auto-increments keys and a non-1NF column for amounts. An
order was shorted and in Black Africa, you cannot call up and get more
supplies the next day. Thanks to bad DB design, there were five dying
children and enough medicine to save three; you are in the field and
have to make a decision NOW, NOW, NOW.

I just found out that the decision was to under-medicate. Four are
alive, but two of them have permanent eye and ear damage; the fifth
died. If someone had designed the DB correctly at the start all five
would be alive and healthy.

Profesional, proper rewrite or a kludge to get the old system to the
new platform. Make a decision NOW, NOW, NOW.