Hi. I'm trying to use an auto increment field in SQL, but it isn't quite doing what I want. I have a parent table and the child tables are set up with IDENTITY. The only problem is that SQL auto increments over all the tables, instead of auto-incrementing for each table. For example, KB table 1 has CDSS_key fields 1, 3, 4, 6 and KB table 2 has CDSS_key fields 2, 5, 7. I would like to have KB table 1 to have CDSS_key fields 1, 2, 3, 4 and KB table 2 to have CDSS_key fields 1, 2, 3, 4. Can anyone help? This is my create script:
I'm sorry but you can't do this automatically. You would need to create your index field as a simple integer value and then write code that would update new values to the maximum existing value for the CDSS_app + 1. You could put it in a trigger, or in the stored procedure used to populate the table.
Generally, an issue such as this indicates a problem with the database design. I encourage you to rethink your application and see if you can come up with a better implementation.