SQL Express confusion...setting primary key?

[darrel]

I have two tables in my SQL Express DB within my application.

On one table, I can right-click on the row and set it to be the primary key.
And it is the primary key.

On the other table, though, when I right click to set the primary key, the
key icon shows up, but it doesnt' update the 'IDENTITY SPECIFICAION' below
in the properties to YES like the other table.

Why is that?

-Darrel

[bruce barker]

they have nothing to do with each other. to make a column an identity
column it must be a numeric column, then you set the property.

-- bruce (sqlwork.com)

darrel wrote:

I have two tables in my SQL Express DB within my application.

On one table, I can right-click on the row and set it to be the primary key.
And it is the primary key.

On the other table, though, when I right click to set the primary key, the
key icon shows up, but it doesnt' update the 'IDENTITY SPECIFICAION' below
in the properties to YES like the other table.

Why is that?

-Darrel

[darrel]

they have nothing to do with each other.

What's the difference between them?

to make a column an identity column it must be a numeric column, then you
set the property.

It's set to 'int' right now, but I still can't change the Identity
Specification for some reason.

-Darrel

[Mark Fitzpatrick]

A primary key simply means the values in a field need to be unique. An
identity means that the field will have an integer value and will increment.
The two items are often used together, but they don't have to be. For
example, a primary key could be the two-letter abbreviation for all the US
states, there's no need for the records to have an identity row if they
already have a unique value.

Do you have any data in the table that you are trying to create the identity
row on? It could be that you already have null values in the field you are
trying to change. In this case, you can't do that since sql server express
doesn't know how to convert it into an identity. You may have to populate
the field ahead of time with a series of numbers first. Not too hard to do
with SQL, or you can create a new temporary field, set it as an identity and
hit save and hope sql server can fill in the values for you, then delete the
old row and rename the new temporary one to the name of the old, problematic
row.

--

Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

"darrel" <no*****@nowhere.comwrote in message
news:OC**************@TK2MSFTNGP06.phx.gbl...

>they have nothing to do with each other.

What's the difference between them?

> to make a column an identity column it must be a numeric column, then
you set the property.

It's set to 'int' right now, but I still can't change the Identity
Specification for some reason.

-Darrel

[darrel]

>A primary key simply means the values in a field need to be unique. An

>identity means that the field will have an integer value and will
increment. The two items are often used together, but they don't have to
be. For example, a primary key could be the two-letter abbreviation for all
the US states, there's no need for the records to have an identity row if
they already have a unique value.

Ah! Yes, that makes sense. Thanks for the explanation.

Do you have any data in the table that you are trying to create the
identity row on? It could be that you already have null values in the
field you are trying to change.

I did, but none of it was null. I then deleted all the data so it was an
empty table, and I still couldn't set the field to be an identity.

I then created a new table. I STILL can't set a field to be the Identity.
(Identity Specification = Yes). I'm stumped. It's set to INT data type allow
NULLS is NOT checked.

-Darrel

[darrel]

I then created a new table. I STILL can't set a field to be the Identity.

(Identity Specification = Yes). I'm stumped. It's set to INT data type
allow NULLS is NOT checked.

Ugh. I'm an idiot.

You have to EXPAND the 'Identity Specification' item in the properties pane
and then change the IS IDENTITY property there.

'doh!

-Darrel