#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0

    IDENTITY on 2 PRIMARY KEY's


    I have a MS SQL problem. I used to do this (AUTO_INCREMENT on 2 primary key) in MY SQL and it works,

    Example:

    CREATE TABLE blah (
    col1 INT AUTO_INCREMENT,
    col2 INT,
    PRIMARY KEY (col2, col1)
    )

    So, when i do the following queries:
    INSERT INTO blah (col2) VALUES (1)
    INSERT INTO blah (col2) VALUES (1)
    INSERT INTO blah (col2) VALUES (3)
    INSERT INTO blah (col2) VALUES (3)

    I would get the following table with SELECT * from blah

    col2 | col1
    ------------
    1 | 1
    1 | 2
    3 | 1
    3 | 2

    This does not seem to work in MS SQL with IDENTITY. Can anyone help?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,202
    Rep Power
    4279
    primary keys must, by definition, be null, so that was my first thought

    however, i just tested your code, and it worked fine!
    Code:
    create table compositepk
    ( col1 integer  identity
    , col2 integer 
    , primary key (col1, col2)
    );
    insert into compositepk (col2) values ( 23 );
    insert into compositepk (col2) values ( 34 );
    insert into compositepk (col2) values ( 45 );
    
    select * from compositepk;
    
    col1 col2
    1    23
    2    34
    3    45
    what do you mean, "does not seem to work"?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0
    It works, but if i do that in MS SQL (replacing AUTO_INCREMENT with IDENTITY), the result from select * from blah is not the same.

    The result will be:

    col2 | col 1
    -------------
    1 | 1
    1 | 2
    3 | 3
    3 | 4
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    6
    Rep Power
    0
    Originally posted by r937
    primary keys must, by definition, be null, so that was my first thought

    however, i just tested your code, and it worked fine!
    Code:
    create table compositepk
    ( col1 integer  identity
    , col2 integer 
    , primary key (col1, col2)
    );
    insert into compositepk (col2) values ( 23 );
    insert into compositepk (col2) values ( 34 );
    insert into compositepk (col2) values ( 45 );
    
    select * from compositepk;
    
    col1 col2
    1    23
    2    34
    3    45
    what do you mean, "does not seem to work"?
    It works but not the the way MySQL would produce the table.

    MySQL would produce this table
    col1 col2
    1 23
    1 34
    1 45

    and if you do this query again (in mysql)
    insert into compositepk (col2) values ( 23 );
    then the result will be
    1 23
    2 23
    1 34
    1 45
    Last edited by okidoki; February 7th, 2004 at 01:15 AM.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,202
    Rep Power
    4279
    oh wow, that is so cool!!

    i never knew it could do that

    thanks for the info

    no, i don't think sql server works that way

IMN logo majestic logo threadwatch logo seochat tools logo