|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
IDENTITY Column!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 IDENTITY 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. Show quote "Arpan" <arpan***@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 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. Show quote "Arpan" <arpan***@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 > You got everything wrong. Please read a book on RDBMS.
>> A table named Products1 containing 20 records has the following columns: << Rows are not records; IDENTITY cannot ever be a relational key. I findit 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) ); >> I created another table named Products2 whose design is exactly the same as the design of the table named Products1 << One of the basic ideas of RDBMS is that each table is a set of the samekind 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! >> SET IDENTITY_INSERT Products2 ON << This is a dangerous option used with a non-relational, proiprietaryfeature 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  |
|||||||||||||||||||||||
Other interesting topics