Best way to insert data into tables without primary keys

SQL Apprentice Question
I am working on a SQL Server database in which there are no primary
keys set on the tables. I can tell what they are using for a key. It
is usually named ID, has a data type of int and does not allow nulls.
However, since it is not set as a primary key you can create a
duplicate key.

This whole thing was created by someone who is long gone. I don't
know how long I will be here and I don't want to break anything. I
just want to work with things the way they are.


So if I want to insert a new record, and I want the key, which is
named ID, to be the next number in the sequence, is there something I
can do in an insert sql statement to do this?



Celko Answers
>> I am working on a SQL Server database in which there are no primary keys set on the tables. <<


By definition, it is not a table at all, but a simple file written with
SQL ..


>> I can tell what they are using for a key. It is usually named ID, has a data type of int and does not allow nulls.<<


Ah yes, the Magical, Universal "id" that God put on all things in
creation. To hell with ISO-11179 and metadata, to hell with Aristotle
and the law of identity!


>> However, since it is not set as a primary key you can create a duplicate key. <<


Duplicate key is an oxymoron


>> This whole thing was created by someone who is long gone. I don't know how long I will be here and I don't want to break anything. <<


A better question; how long can an enterprise with a DB like this
survive? I'd be updating the resume and stealing office supplies.


>> > So if I want to insert a new record [sic], and I want the key, which is named ID, to be the next number in the sequence, is there something I can do in an insert statement to do this? <<


Rows are not anything like records; the failure of the first guy to
understand this is why he mimiced a magnetic tape file system's record
numbers instad of providing a relational key.

The stinking dirty kludge is to use "SELECT MAX(id)+1 FROM Foobar" in
the INSERT INTO statements. Oh, you also need to check for dups and add
a uniqueness constraint (mop the floor and fix the leak).


The right answer is to re-design this system properly.