Auto increment in SQL

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:

CREATE TABLE CDSS (
CDSS_app char(10),
timestamp datetime DEFAULT getdate(),
PRIMARY KEY (CDSS_app) )

CREATE TABLE KB (
CDSS_key int IDENTITY(1, 1),
submit_by char(50),
timestamp datetime DEFAULT getdate(),
common_prob char(100),
prob_sol char(511),
CDSS_app char(10),
PRIMARY KEY (CDSS_key),
FOREIGN KEY (CDSS_app) REFERENCES CDSS (CDSS_app) )Please explain more clearly. KB table 1? KB table 2? What are they really named? Do they have identical structures? I only see you creating a single "KB" table.

blindman|||Originally posted by blindman
Please explain more clearly. KB table 1? KB table 2? What are they really named? Do they have identical structures? I only see you creating a single "KB" table.

blindman

Here's the script. The results are below. Thanks in advance!

CREATE TABLE CDSS (
CDSS_app char(10),
timestamp datetime DEFAULT getdate(),
PRIMARY KEY (CDSS_app) )

INSERT INTO CDSS (CDSS_app) VALUES ('OASIS')
INSERT INTO CDSS (CDSS_app) VALUES ('CIS')

CREATE TABLE KB (
CDSS_key int IDENTITY(1, 1),
submit_by char(50),
timestamp datetime DEFAULT getdate(),
common_prob char(100),
prob_sol char(511),
CDSS_app char(10),
PRIMARY KEY (CDSS_key),
FOREIGN KEY (CDSS_app) REFERENCES CDSS (CDSS_app) )

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test1', 'test1', 'test1', 'OASIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test2', 'test2', 'test2', 'OASIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test3', 'test3', 'test3', 'CIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test4', 'test4', 'test4', 'OASIS')

SELECT CDSS_key, CDSS_app FROM KB

Results:

CDSS_key CDSS_app

1 OASIS
2 OASIS
3 CIS
4 OASIS

I want it to be:

CDSS_key CDSS_app

1 OASIS
2 OASIS
1 CIS
3 OASIS|||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.

blindman