"darrel" <notreal@nowhere.com> wrote in message
news:ete7p3$bab$1@forums.macromedia.com...
> Is the 'fix' for this to just add a new column to the
public facing tables
> with a PK? Better method?
>
> -Darrel
You're confusing a primary key with an identity column.
Primary key is not the same as identity or autonumber
Primary key <> identity/autonumber
Primary key != identity/autonumber
Sorry, pet peeve. :)
A primary key can be anything at all, or even span more than
one column.
Its only requirement is that it identifies a row uniquely. An
identity is
an auto-incrementing integer column that takes a seed (first
value) and
increment (the amount added to the last value for the next
value). A
primary key constraint does not set any values; that's a
property shared by
the IDENTITY property and DEFAULT constraints.
Are you noticing a performance problem on your databases? A
primary key
creates a clustered index for you; without a clustered index,
your table is
just a heap. You can certainly create a clustered index on
columns other
than the key columns, but you need to have one somewhere in
the table.
On the public database, add the primary key constraint. Just
don't make the
column an identity column.
So for the internal DB, you'd have:
CategoryID INT NOT NULL IDENTITY(1,1)
And on the public DB you'd have:
CategoryID INT NOT NULL
On *both* DB's you'll have:
PRIMARY KEY(CategoryID)
To add the key to an existing table, run the following
statement (you can
add a key constraint to an existing column with no problems,
so long as the
data in that column is already unique):
ALTER TABLE <table name>
ADD PRIMARY KEY(<column name(s)>)
GO
If you've got a clustered index elsewhere in the table, you
may have to add
the NONCLUSTERED keyword. If you don't know, then LEAVE IT
OFF - let the DB
throw an error first. The clustered index is very important,
and you should
always have indexes on columns that are used in joins.
5
Replies
AdChoices