Results 1 to 13 of 13

Thread: Unique Field

  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Unanswered: Unique Field

    I have a database table with a primary key that auto-increments - using MS SQL Server 2000.
    However I have another field (standard field) that must not allow duplicates.

    Besides writing code to sift through the database every time to check for duplicates (which would take long to search through the db every time), is there any setting in MS SQL Server 2000 that allows for an automatic check for duplication and will prevent duplicates!??

    Here's an example of what I mean...

    PK Entry_Sheet Cust_Id Date
    1 62345 cust1 16/11/2004
    2 45663 cust6 16/11/2004
    3 82917 cust1 16/11/2004
    4 19283 cust2 17/11/2004
    5 28764 cust2 18/11/2004

    I have everything created, with data already in the table. What I need now is some way to prevent duplicates from occurring.
    The Entry_Sheet value needs to be unique. Instead of writing code to run through the entire db each time an insert is performed, is there a simpler way using a SQL Server DB!??

  2. #2
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up Use Unique constraint

    You can use UNIQUE constraint on the column which is not part of the primary key, UNIQUE constraint will make sure that there are no duplicates.

    You can also use UNIQUE constraint with a combination of columns instead of just one column.

    Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow the value NULL. However, as with any value participating in a UNIQUE constraint, only one NULL value is allowed per column.

    If you do not want to include NULL value also then u can use NOT NULL constraint with UNIQUE constraint.

    Hope this helps...
    Sudar

    --
    My Blog

  3. #3
    Join Date
    Nov 2004
    Posts
    7
    I've created an index with my unique constraint (in SQL Server 2000), but it still saves duplicates in the field!!

  4. #4
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    Are you sure that you have created a unique index on the field that you were talking about. ?

    If a table has unique index defined on a particular field, then further INSERT or UPDATE statements that insert or update duplicate values for that field will generate an error.

    Double check that you have included the column while creating the index.

    BTW, what data type is that column..?
    Sudar

    --
    My Blog

  5. #5
    Join Date
    Nov 2004
    Posts
    7
    This is the table I'm talking about!
    WCD_No needs to be unique!
    WM_id is the primary key!
    I've created a new index & made WCD_No a unique constraint, however I am still being allowed to enter a duplicate WCD_No! (Using Delphi 7 also!)


    Col Name Data Type Length

    WM_id numeric 9
    C_id numeric 9
    LP_id numeric 9
    CD_id numeric 9
    WM_Type varchar 10
    WCD_No numeric 9
    Invoice_No varchar 20
    Entry_DtTm datetime 8
    Entry_Date datetime 8
    Entry_Day varchar 10
    Entry_Month varchar 10
    Posted_Date datetime 8
    Closing_Date datetime 8
    Total_kgs numeric 9
    Total_Qty numeric 9
    Total_Nett numeric 9
    Total_Disc numeric 9
    Total_Vat numeric 9
    Total_Gross numeric 9
    Order_No varchar 25
    Inc_Exl varchar 1
    Discount numeric 9
    Inv_Mess1 varchar 25
    Inv_Mess2 varchar 25
    Inv_Mess3 varchar 25
    Sales_Code varchar 5
    Cost_Code varchar 5
    SetTrm_Code varchar 2
    WCD_Status varchar 1
    WCD_Invoiced varchar 1
    Cust_Code varchar 6
    Cust_Desc varchar 40
    Cust_Reg varchar 50
    Vehicle_No varchar 50
    AutoCl_Kgs numeric 9
    AutoCl_Qty numeric 9
    Incin_Kgs numeric 9
    Incin_Qty numeric 9

  6. #6
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    Try adding the UNIQUE constraint to the table itself instead of adding it to the Index.

    You can add the constraint by using the Alter Table statement.
    Sudar

    --
    My Blog

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't see how that can be. Please post the CREATE TABLE code that you used for us, something has to be wrong with the UNIQUE CONSTRAINT declaration if you are still able to enter multiple rows.

    -PatP

  8. #8
    Join Date
    Nov 2004
    Posts
    7
    I didn't create the table! The person who did create it isn't around! I'm trying to fix it for someone else tho! Can I use the 'alter' statement and make wcd_no unique while it has data in it or do I have to start fresh?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can use ALTER TABLE, but if you want to leave the rows in the table that already have duplicate values you'll need to use the WITH NOCHECK parameter.

    -PatP

  10. #10
    Join Date
    Nov 2004
    Posts
    7
    Basically, everything has already been created & the db has loads of info. However WCD_No was never made unique! I need to do this now, but is it possible (without writing loads of code) & will the fact that there are already duplicates in the db for WCD_No affect this?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if there are already duplicates, you will have to resolve them before you can add the unique constraint!!!

    select * from yourtable
    where WCD_no in
    ( select WCD_no from yourtable
    group by WCD_no having count(*) > 1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2004
    Posts
    7
    This is the error I receive :

    Server: Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 39. Most significant primary key is '<NULL>'.
    Server: Msg 1750, Level 16, State 1, Line 1
    Could not create constraint. See previous errors.
    The statement has been terminated.


    Is there any way around this or do I have to delete duplicates?
    (How do I track ID 39?)

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see post #11 -- "if there are already duplicates, you will have to resolve them before you can add the unique constraint"

    you need to find all the duplicate keys first

    see post #11
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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