Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: 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) )

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  3. #3
    Join Date
    Nov 2003
    Posts
    3
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •