December 1, 2004 at 11:38 am
Hoping I can explain this properly...
I have a field (INT, no nulls) that has a clustered Primary Key Index. They would like to be able to allow duplicates in this field. I "THINK" what I have to do is edit the Index and turn on the 'ignore duplicates' value. Is this correct?
Not sure what else this will affect. I guess what I'm confused about is - if there was no PK index, does that mean duplcates are, by default, allowed in all fields???
Thanks.
December 1, 2004 at 11:57 am
As long as there are either primary or unique constraint defined in specific columns, you will not be able to insert duplicate data in these columns.
Turn on "ignore_dup_key" controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
December 1, 2004 at 2:17 pm
Be aware that you are violating one of the basics of relational tables - a key is a value or a combination of values that is unique for every row. If you have an OLAP database, then having a unique row may not be as important, but in an OLTP database destroying your primary key will not get you a nomination for database administrator of the month.
Most of the time we find the duplicate values and decide what should be done with them.
Keys are also used to reference between tables, and foreign keys must be unique.
If the data in your table is not unique you should consider adding a column that will be.
As for your second question - as long as you have a key that is unique, yes, the other columns can be duplicates. For example, if there are two John Smiths in your Sales Department and they share an address, and so forth, then the key (an employee number, or something like that) would make each row unique.
Quand on parle du loup, on en voit la queue
December 1, 2004 at 2:31 pm
If you want to allow a column to have dupes, then don't set that column as PK.
Right, if there is no PK, by default, it allows dupes.
To avoid the case when two rows are identical and you want to delete one of them you should create a column that uniquely identifies itself. If you don't have any column or combined columns that can uniquely identify a row, then you can create an identity column.
December 2, 2004 at 6:27 am
Great! Thank you all so much for the input!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply