IDENTITY Column!

A table named Products1 containing 20 records has the following
columns:
PID int IDENTITY (1,1)
PCode varchar(50)
PName varchar(50)
PDesc varchar(500)
Price money
Qty int
I created another table named Products2 whose design is exactly the
same as the design of the table named Products1 i.e. the PID column in
Products2 is also an IDENTITY(1,1) column. I issued the following query
to populate Products2:
---
SET IDENTITY_INSERT Products2 ON
GO
INSERT INTO Products2 (PID,PCode,PName,PDesc,Price,Qty)
SELECT * FROM Products1
---
The above query, when executed in QA, populates Products2 with the
records existing in Products1 but if the above query is executed again,
Products2 again gets populated with 20 records existing in Products1
which is OK but the PID values of the 2nd set of 20 records remain the
same as that of the first set of 20 records i.e. there are duplicate
PID values but IDENTITY columns are supposed to identify each row
uniquely which the IDENTITY column PID doesn't do here! So does this
mean that PID no longer remains an IDENTITY column? Shouldn't SQL
Server generated an error when the INSERT query was executed for the
second time?
Had the PID column been a PRIMARY KEY column, executing the INSERT
query in QA two (or more) times rightly generates a PRIMARY KEY
Constraint error but why doesn't the same happen with an IDENTITY
column?
Thanks,
ArpanIDENTITY doesn't guarantee uniquness--especially when you're using
IDENTITY_INSERT, something that should only be used when combining tables or
databases (once in a blue moon). You must either have a primary key or
unique constraint on the IDENTITY column.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123968967.850494.94880@.o13g2000cwo.googlegroups.com...
> A table named Products1 containing 20 records has the following
> columns:
> PID int IDENTITY (1,1)
> PCode varchar(50)
> PName varchar(50)
> PDesc varchar(500)
> Price money
> Qty int
> I created another table named Products2 whose design is exactly the
> same as the design of the table named Products1 i.e. the PID column in
> Products2 is also an IDENTITY(1,1) column. I issued the following query
> to populate Products2:
> ---
> SET IDENTITY_INSERT Products2 ON
> GO
> INSERT INTO Products2 (PID,PCode,PName,PDesc,Price,Qty)
> SELECT * FROM Products1
> ---
> The above query, when executed in QA, populates Products2 with the
> records existing in Products1 but if the above query is executed again,
> Products2 again gets populated with 20 records existing in Products1
> which is OK but the PID values of the 2nd set of 20 records remain the
> same as that of the first set of 20 records i.e. there are duplicate
> PID values but IDENTITY columns are supposed to identify each row
> uniquely which the IDENTITY column PID doesn't do here! So does this
> mean that PID no longer remains an IDENTITY column? Shouldn't SQL
> Server generated an error when the INSERT query was executed for the
> second time?
> Had the PID column been a PRIMARY KEY column, executing the INSERT
> query in QA two (or more) times rightly generates a PRIMARY KEY
> Constraint error but why doesn't the same happen with an IDENTITY
> column?
> Thanks,
> Arpan
>|||Thanks, Brian, for your input but BOL states that IDENTITY columns
contain system-generated values that uniquely identify each row within
a table. So is BOL wrong?
Thanks once again,
Regards,
Arpan|||You got everything wrong. Please read a book on RDBMS.
Rows are not records; IDENTITY cannot ever be a relational key. I find
it amazing that you have a product code that changes size and can be
CHAR(50) and NULL, etc. If you knew what you were doing and had posted
DDl, would look like this?
CREATE TABLE Products
(product_id CHAR(13) NOT NULL PRIMARY KEY -- upc' idustry standard
product_name CHAR(20) NOT NULL,
product_descr VARCHAR (250) NOT NULL,
product_price DECIMAL (8,2) NOT NULL
CHECK (product_price > 0.00),
qty_on_hand INTEGER NOT NULL
CHECK (qty_on_hand > 0),
product_status INTEGER DEFAULT 1 NOT NULL
CHECK (product_status IN (1,2) );
One of the basic ideas of RDBMS is that each table is a set of the same
kind of entities. If two tables have the same structure then they
model the same entity. What you probably need is a status code to show
the LOGICAL difference between a table 1 and table 2 products. Surely,
you are not just shifting rows from table to table, to mimic a punch
card or magnetic tape file system!
This is a dangerous option used with a non-relational, proiprietary
feature that should not have been there anyway. You have gone from bad
to worse.
Stop what you are doing. Read a book or two. Start over.|||Well, Celko, I guess you have dug in too deep in the example I have
shown or you are trying to read too much in between the lines. This is
just a hypothetical scenario....definitely not a practical one. Of
course, having 2 such tables just doesn't make any sense. I wanted to
get my doubt on IDENTITY clarified which is why I cited those 2 tables.
Maybe I could have given a better example but couldn't think of
anything else within the stipulated time of 2-3 minutes I was given to
frame my query (I am on my friend's computer)!!
So please take it easy :-)
Thanks,
Regards,
Arpan|||BOL is not wrong. IDENTITY_INSERT bypasses the normal behavior of IDENTITY.
If you don't use IDENTITY_INSERT, then absent a catastrophic system failure,
the generated IDENTITY values will always be unique. That's why it's use
should be limited. There are instances when you want to specify the
identity values, for example, when you're combining databases or tables. A
further limitation is that IDENTITY_INSERT can only be on for one table at a
time per session. It is a tool for a database administrator, to be used
only when absolutely necessary.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123972518.924767.159220@.g47g2000cwa.googlegroups.com...
> Thanks, Brian, for your input but BOL states that IDENTITY columns
> contain system-generated values that uniquely identify each row within
> a table. So is BOL wrong?
> Thanks once again,
> Regards,
> Arpan
>|||Thank you very much, Brian, for helping me clarify my doubt.
Regards,
Arpan