If you failed to insert a new record into a table because of the unique constraint, do not miss to read this article. It will help you to solve this problem.
In this article, we will introduce 4 methods to solve the problem when you fail to insert a new record because it violates the unique constraint of the table in SQL Server.
A Real Case:
Now let’s see a real case. We have a database named “Product” and there is a table called “DataNumenProduct” in the database. There are two fields in the table, i.e., “ProductId” and “ProductName”, with data types of “int” and “varchar” respectively. And “ProductId” is a primary key with unique constraint.
Currently there are some records in the table, as below:
Figure 1 Records in Table “DataNumenProduct”
Assuming there are two DBAs for the database, i.e., Jim and Tom. One day, Jim deleted the first record in the table, as below:
DELETE FROM DataNumenProduct WHERE ProductId=1;
On the second day, Tom inserted a new record into the table, which has the same primary key with the deleted record, as below:
INSERT INTO DataNumenProduct VALUES(1,'DataNumen OutLook Repair');
So our table has the following records, as below:
Figure 2 Updated Records in Table “DataNumenProducts”
Now if Jim wants to restore the deleted record back to the table, it will violate the unique constraint of the primary key “ProductId”. In such a case, we can use one of the workarounds below to solve the problem.
Workaround 1: Modify the Primary Key of the New Record to Prevent Conflict
We can modify the primary key of the record to be inserted to a new value which is different from those of all existing records in the table. Take the above case as an example, for the record to be inserted, we can modify the primary key value from 1 to 5. Then we can restore the new generated record back to table, as below:
INSERT INTO DataNumenProduct VALUES(5, 'DataNumen Access Repair');
Below are the final records in the table:
Figure 3 Final Records in the Table “DataNumen Products”
Workaround 2: Modify the Primary Key of the Existing Record to Prevent Conflict
- First, we need to update the primary key value of the existing record which may cause conflict, as below:
UPDATE DataNumenProduct SET ProductId = 6 WHERE ProductId = 1;
Below are the updated records in the table:
Figure 4 Updated Records in Table “DataNumen Products”
- Then we can restore the deleted record back to the table, as below:
INSERT INTO DataNumenProduct VALUES(1, 'DataNumen Access Repair ');
Now let’s see the final version, as below:
Figure 5 Final Records in Table “DataNumen Products”
Workaround 3: Disable the Unique Constraint Temporarily via SQL
- To solve the problem, we can delete the primary key constraint “pk” in the table temporarily, as below:
ALTER TABLE DataNumenProduct DROP CONSTRAINT pk;
- Then we can add back the deleted record to the table, as below:
INSERT INTO DataNumenProduct VALUES(1, 'DataNumen Access Repair');
Now let’s see the records in the table:
Figure 6 Updated Records in Table “DataNumen Products”
- Then since two records have the same primary key values, we need to modify one of them to prevent conflict, as below:
UPDATE DataNumenProduct SET ProductId = 5 WHERE ProductName = 'DataNumen Outlook Repair';
- At last, we add back the primary key constraint “pk” to the table, as below:
ALTER TABLE DataNumenProduct ADD CONSTRAINT pk PRIMARY KEY(ProductId);
Now there are some records in the table, as below:
Figure 7 Updated Content in the Table “DataNumen Products”
Workaround 4: Disable the Unique Constraint Temporarily via SQL Server Management Studio:
- In the first place, we delete the primary key constraint “pk” in the table via GUI, as below:
Figure 8 Delete the Primary Key Constraint “pk” via GUI
- Then we insert the deleted record in real case in the table, as below.
INSERT INTO DataNumenProduct VALUES(1, 'DataNumen Access Repair');
Now there are some records in the table, as below:
Figure 9 Updated Content in Table “DataNumen Products”
- Then we modify the primary key value of existing record in the table, as below:
UPDATE DataNumenProduct SET ProductId = 5 WHERE ProductName = 'DataNumen Outlook Repair';
- At last, we add back the primary key constraint “pk” to the table via GUI, as below:
Figure 8 Add Back the Primary Key Constraint “pk” via GUI
Solve SQL Server Database Corruption
Other than Unique constraints, SQL Server corruption is also a very common problem annoying DBAs. Normally we can use SQL Server built-in commands to solve the corruptions. However, if they do not work, then we can resort to some third-party SQL Server data recovery tools.
Author Introduction:
Jim Hu is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Access repair and dbf repair software products. For more information visit www.datanumen.com
Leave a Reply