In my previous posts I tried to provide some key information to stimulate your interest in SAP Sybase products. If you are an application developer on any SAP platform, it will pay to know more about Adaptive Server Enterprise ( the former Sybase ASE) platform as the SAP business suite is successfully ported on it now. If you are just starting to explore ASE, you will be in a similar boat as I was about 2 years back. As I work more and more on it for various projects, more and more things continually surprise me. Having come to ASE from other popular databases, some of the features about ASE totally threw me a curve ball.
One such feature is called IDENTITY. This is very similar to AUTOINCREMENT column in other databases. IDENTITY is an automatically incremented counter, the value of which can be assigned to a column of a table. As one would expect, you can assign a starting value for the column and ASE increments the value with every insert into the table without any problem. In addition, ASE allows you to bulk add records to any table with an identity column, there by accepting explicit identity column values.
It's important to understand that IDENTITY columns are routinely used as primary keys on a table because of its auto-increment capability.You might also believe that the name 'IDENTITY' itself suggests that the value of the column in each row is unique and it is, as long as you leave it to ASE but the strange thing is ASE does not create a unique constraint on the column, there by leaving it to the user to keep it unique when IDENTITY_INSERT on the table is turned ON. By using this option, the table owner or db administrator can insert any number of duplicate entries into IDENTITY column as they want, which in my mind defeats the purpose of having an identity column. By default IDENTITY_INSERT is turned off, hence identity column receiving a duplicate entry is not normal. But once you turn it ON for any reason, you have to be extra careful while entering values manually. You can avoid this problem by denoting the identity column as a primary key column. Which bring me to the next point, how to assign a primary key to the table in ASE.
There are two ways you could do it, using Create or Alter Table syntax or through sp_primarykey stored procedure. A thing you may want to keep in mind while using sp_primarykey is that the stored procedure does not create a unique constraint on the column, which again is a huge surprise to me. Here is what ASE documentation says about sp_primarykey stored procedure.
So a unique combination of denoting a column as identity column and a primary key you will have unintended consequences of getting duplicate values in the identity primary columns. Here is an example.\
CREATE TABLE test (id int IDENTITY, desc varchar(10))
sp_primarykey test, id
SET IDENTITY_INSERT test OFF
INSERT INTO test (desc) VALUES('abcd')
SET IDENTITY_INSERT test ON
INSERT INTO test(id, desc) VALUES(1, 'fghk')
Now you have two rows with duplicate id value of 1. Neither, defining as IDENTITY nor PRIMARY KEY could prevent getting a duplicate value in the id column. This could have disaster effect on your business application if it relies on this column to get a unique row. So, to avoid this type of situation follow the guidelines as below.
Always define primary keys through Create or Alter table syntax.
Check the uniqueness of identity column of table after you insert its value manually OR create a unique constraint on the identity column right after table creation.
If you are turning IDENTITY_INSERT on then turn it off in the same transaction, so as to avoid any other transaction in the same session adding a duplicate value to identity. It should be somewhat reassuring to developers that the change in IDENTITY_INSERT setting is not persistent. It's persistence is lost once the session ends.
I hope this important point helps you use IDENTITY carefully and saves you some trouble shooting in case of erroneous results from your business application. Happy development.
One such feature is called IDENTITY. This is very similar to AUTOINCREMENT column in other databases. IDENTITY is an automatically incremented counter, the value of which can be assigned to a column of a table. As one would expect, you can assign a starting value for the column and ASE increments the value with every insert into the table without any problem. In addition, ASE allows you to bulk add records to any table with an identity column, there by accepting explicit identity column values.
It's important to understand that IDENTITY columns are routinely used as primary keys on a table because of its auto-increment capability.You might also believe that the name 'IDENTITY' itself suggests that the value of the column in each row is unique and it is, as long as you leave it to ASE but the strange thing is ASE does not create a unique constraint on the column, there by leaving it to the user to keep it unique when IDENTITY_INSERT on the table is turned ON. By using this option, the table owner or db administrator can insert any number of duplicate entries into IDENTITY column as they want, which in my mind defeats the purpose of having an identity column. By default IDENTITY_INSERT is turned off, hence identity column receiving a duplicate entry is not normal. But once you turn it ON for any reason, you have to be extra careful while entering values manually. You can avoid this problem by denoting the identity column as a primary key column. Which bring me to the next point, how to assign a primary key to the table in ASE.
There are two ways you could do it, using Create or Alter Table syntax or through sp_primarykey stored procedure. A thing you may want to keep in mind while using sp_primarykey is that the stored procedure does not create a unique constraint on the column, which again is a huge surprise to me. Here is what ASE documentation says about sp_primarykey stored procedure.
- Executing sp_primarykey adds the key to the syskeys table. Only the owner of a table or view can define its primary key. sp_primarykey does not enforce referential integrity constraints; use the primary key clause of the create table or alter table command to enforce a primary key relationship.
- Define keys with sp_primarykey, sp_commonkey, and sp_foreignkey to make explicit a logical relationship that is implicit in your database design. An application program can use the information.
- A table or view can have only one primary key. To display a report on the keys that have been defined, execute sp_helpkey.
- The installation process runs sp_primarykey on the appropriate columns of the system tables.
So a unique combination of denoting a column as identity column and a primary key you will have unintended consequences of getting duplicate values in the identity primary columns. Here is an example.\
CREATE TABLE test (id int IDENTITY, desc varchar(10))
sp_primarykey test, id
SET IDENTITY_INSERT test OFF
INSERT INTO test (desc) VALUES('abcd')
SET IDENTITY_INSERT test ON
INSERT INTO test(id, desc) VALUES(1, 'fghk')
Now you have two rows with duplicate id value of 1. Neither, defining as IDENTITY nor PRIMARY KEY could prevent getting a duplicate value in the id column. This could have disaster effect on your business application if it relies on this column to get a unique row. So, to avoid this type of situation follow the guidelines as below.
Always define primary keys through Create or Alter table syntax.
Check the uniqueness of identity column of table after you insert its value manually OR create a unique constraint on the identity column right after table creation.
If you are turning IDENTITY_INSERT on then turn it off in the same transaction, so as to avoid any other transaction in the same session adding a duplicate value to identity. It should be somewhat reassuring to developers that the change in IDENTITY_INSERT setting is not persistent. It's persistence is lost once the session ends.
I hope this important point helps you use IDENTITY carefully and saves you some trouble shooting in case of erroneous results from your business application. Happy development.
