![]() ![]() cannot add a name that exists and has an ignored duplicate Let us test this solution: - cannot add a name that existsĬannot insert duplicate key row in object 'dbo.Party' with unique index 'UNQ_Party_UniqueNewNames'. Non-grandfathered records just have NULL, which is not unique.ĬREATE UNIQUE INDEX UNQ_Party_UniqueNewNames ON dbo.Party(Name, IgnoreThisDuplicate) It works because the grandfathered duplicates have a unique value (= their ID). **THIS** is our pseudo-unique constraint. It prevents granting further exemptions beyond the ones we made above. This constraint is not strictly necessary. *Secondary* instances will be set to their ID (a unique value). The *first* instance will be left NULL. ![]() Let us ignore existing ones, and ensure that no new duplicates can be added: - Add a new column to mark grandfathered duplicates.ĪLTER TABLE dbo.Party ADD IgnoreThisDuplicate INT NULL Here is a table with duplicates: CREATE TABLE dbo.Party ![]() It seems to me from the comments that I have to do this logic in the stored procedures. While this is wrong, it's acceptable in our use case. For the few existing duplicates this will mean that multiple rows are returned and displayed. If it was possible to have SQL check the uniqueness on its own, that would be preferable. One approach is to have the INSERTs use a stored procedure which will fail for duplicates. In this case I have a table which ties licensing information to "CompanyName"ĮDIT: Having multiple rows with the same "CompanyName" is bad data, but we can't remove or update those duplicates at this time. I have tried using NOCHECK but was unsuccessful. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added.Ĭan I create a UNIQUE that doesn't check for existing compliance? I have a table which currently has duplicate values in a column. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |