add column

I have a table with only about 40 rows. For some reason we accidentally
forgot the primary key. Is it possible to add a column to this table that
adds a number to each row and also adds an increment of 1 when a new item is
added. While we are at it can we make this the primary key column.
I am new at this but I will take a stab. Maybe someone can look at my syntax
and tell me if I am on the right track.
the table name is tblProductSubcategories the column I need to add will be
called intSubcategoryID
Here is my go at it.
Alter Table tblProductSubcategories
Add
intSubcategoryID int identity not null
I know that is not exactly what is needed. Can someone help me with this
syntax. Much appreciated.
Laura K>> I have a table with only about 40 rows. For some reason we
accidentally forgot the primary key. <<
That is not an accident; that is a major design flaw.
each row and also adds an increment of 1 when a new item is added.
While we are at it can we make this the primary key column. <<
BY DEFINITION this is not a primary key!! Start over and do a real
design.|||It's almost done. You just forgot the primary key. Try this
Alter Table tblProductSubcategories
Add
intSubcategoryID int identity not null <B>PRIMARY KEY</B>
--
Lionel
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||YES!! Just add another Column defined as Integer Identity... Say it's
Customers table, and the new PK will be CustomerID...
Alter Table Customers
(Add Column CustomerID Integer Identity Primary Key Not Null)
And there's NOTHING at all wrong with using this type of Primary Key... But
you should also, if at all possible, add another "key" in the form of a
unique constrraint or index, on whichever combination of "real" data columns
uniquely identifies each row... This will prevent the additon of two rows
(with different Idnetity values) from being added that represent the same
real world business object.
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||Laura,
YES, you can!! Just add another Column defined as "Integer Identity
Primary Key"... Say it's Customers table, and the new PK will be CustomerID.
.
Alter Table Customers
(Add Column CustomerID Integer Identity Primary Key Not Null)
And there's NOTHING at all wrong with using this type of Primary Key... But
you should also, if at all possible, add another "key" in the form of a
unique constrraint or index, on whichever combination of "real" data columns
uniquely identifies each row... This will prevent the additon of two rows
(with different Idnetity values) from being added that represent the same
real world business object.
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||Sorry, i forgot to remove the html tags from my answer. It should read:
Alter Table Employee
Add
intSubcategoryID int identity not null PRIMARY KEY
Lionel
"Lionel" wrote:
> It's almost done. You just forgot the primary key. Try this
> --
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null <B>PRIMARY KEY</B>
> --
>
> Lionel
>
> "Laura K" wrote:
>|||Thanks all will give it a try this evening. We actually didn't forget the
PK. We decided to use the subcat name but as I designed I got frustrated
with not having a number. Thus the change. Now when I do this will it add
the numbers incrementally to all 40 rows.
Laura
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C8B1B527-538E-438C-A19A-26E665C25645@.microsoft.com...
> Laura,
> YES, you can!! Just add another Column defined as "Integer Identity
> Primary Key"... Say it's Customers table, and the new PK will be
> CustomerID...
> Alter Table Customers
> (Add Column CustomerID Integer Identity Primary Key Not Null)
> And there's NOTHING at all wrong with using this type of Primary Key...
> But
> you should also, if at all possible, add another "key" in the form of a
> unique constrraint or index, on whichever combination of "real" data
> columns
> uniquely identifies each row... This will prevent the additon of two rows
> (with different Idnetity values) from being added that represent the same
> real world business object.
>
> "Laura K" wrote:
>|||WOW you are so very helpful. I wish everyone could be as dramatic as you.
It really helps moral.
Laura K
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116025782.902845.162170@.f14g2000cwb.googlegroups.com...
> accidentally forgot the primary key. <<
> That is not an accident; that is a major design flaw.
>
> each row and also adds an increment of 1 when a new item is added.
> While we are at it can we make this the primary key column. <<
> BY DEFINITION this is not a primary key!! Start over and do a real
> design.
>|||Yes, Although the order will be arbitrary... If you want the records
"numbered" in a specific Order, then you need to do something a bit
different... RU interested int that?
(Remember, since this is a "Surrogate" key, the actual value should not be
publicly visible or used by end-users, so the value shouldn't be important,
just that it's unique...)
"Laura K" wrote:

> Thanks all will give it a try this evening. We actually didn't forget th
e
> PK. We decided to use the subcat name but as I designed I got frustrated
> with not having a number. Thus the change. Now when I do this will it ad
d
> the numbers incrementally to all 40 rows.
> Laura
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:C8B1B527-538E-438C-A19A-26E665C25645@.microsoft.com...
>
>|||Joe,
Jeez, -- Only "killing people"?
You mean the fate of the entire space-time continumn is not at risk from
the use of Identity columns ? What a relief!! And all this time I thought..
.
THis is really good news !!
<grin>
Charly
"--CELKO--" wrote:

> My purpose is not to make you feel good. One of my purposes on
> newsgroups is to keep incompetent progammers from killing people. But
> all that matters in your world is your feelings, isn't it?
> If you don't know me or understand that statement, then Google prior
> postings about how people who screwed up schema designs by not knowing
> what 1NF was and a medical supply program to Africa I consulted on.
> You will probably kludge it with an IDENTITY column. But it needs a
> total re-design.
>