Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    27

    Unanswered: Puzzling Primary Key Problem

    Hi,

    I wonder if anybody encountered before with tables which has records with some of the primary keys as null values?

    Currently I've found a number of records which isn't suppose to be inserted in the first place from a table.

    My table size is over a few millions and currently using SQL Server Standard version

    Seems like the primary key got corrupted or something...

    Anybody encountered this before??

    -deb-

  2. #2
    Join Date
    Mar 2004
    Posts
    6
    If the primary key of the table is autogenerated then you can use

    DBCC CHECKIDENT

    for Checking the current identity value for the specified table and, if needed, corrects the identity value.

  3. #3
    Join Date
    Aug 2003
    Posts
    27

    Unhappy

    Thanks but I don't think that's the case because it happens to a non identity column.
    By Default even before inserting it SQL Server should have prompted a Primary Key Violation Error but in this case it doesn't and proceed to insert the value into the column as null.

  4. #4
    Join Date
    Mar 2004
    Posts
    6

    Lightbulb

    Can you execute a DBCC Checktable statement on the table and verify the results.

  5. #5
    Join Date
    Aug 2003
    Posts
    27
    Will try and see and let you know as the db is at customer's site.
    Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

    here is what [BOL] has to say on the matter

    "When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

    No null values
    No duplicate values

    If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
    "

    so i tried this

    create table nulltesttable
    (
    nullcolumn varchar(10) not null
    )

    and added three rows

    insert nulltesttable values (char(9))
    insert nulltesttable values (char(13))
    insert nulltesttable values (char(32))

    then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

    i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

    select Char(nullcolumn) as 'Null column'
    from nulltesttable

    there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...

  7. #7
    Join Date
    Aug 2003
    Posts
    27
    Thanks... actually that was what I suspect also due to the reason the values are from barcode and sometimes the reader can return some rubbish that is not visible. There are a few records which this rubbish data.

    But still I need to look for other possibilities. For my case if I were to select out using a select statement

    select * from table
    where column is null

    It'll return records.

    That's the very funny thing*sigh*


    Originally posted by Ruprect
    do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

    here is what [BOL] has to say on the matter

    "When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

    No null values
    No duplicate values

    If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
    "

    so i tried this

    create table nulltesttable
    (
    nullcolumn varchar(10) not null
    )

    and added three rows

    insert nulltesttable values (char(9))
    insert nulltesttable values (char(13))
    insert nulltesttable values (char(32))

    then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

    i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

    select Char(nullcolumn) as 'Null column'
    from nulltesttable

    there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...

Posting Permissions

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