Feeds:
Posts
Comments

Archive for the ‘SQL Constraint and Keys’ Category

SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

Posted in PostADay, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 29, 2013 | 2 Comments »

Here is an email I received during the weekend.

“Hi Pinal,

I am a senior tester in the leading organization and we have two different environments 1) Testing 2) Production.

As a part of the testing we want to insert garbage data into the database system and see how the application behaves in this scenario. However, there is a small problem. Everytime when I try to insert garbage data in the database system the tables start giving me error that due to constraints on the table, I need to populate data in certain order and it has to be correct. Actually, we want to populate the bad data and see how application reacts to it. Upon talking to development team regarding this they suggested that we should skip this test as due to contraints there will be never bad data. Though, I understand their viewpoint, I must document how the application behaves when there are bad data and data integrity is compromised.

Is there any way I can disable all the Constraint temporarily and load the random data, test my system and later delete all the inserted data and enable the Constraint back?”

This is indeed a great question, I often come across this question again and again. Here is a quick script I have written in my early career which I still use it when I need to do something similar.

-- Disable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Remember above script when executed right away enable or disable constraints so be extremely careful to execute on production server.

There is one more thing, when you have disabled the constraint, you can delete the data from the table but if you attempt to truncate the table, it will still give you an error. If you need to truncate the table you will have to actually drop all the constraints. Do you use similar script in your environment? If yes, please leave a comment along with the script and I will post it on blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

Read Full Post »

SQL SERVER – Fix: Error: 1505 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name and the index name

Posted in PostADay, SQL, SQL Authority, SQL Constraint and Keys, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on February 5, 2013 | 1 Comment »

Here is another example where the error messages are very clear but often developers get confused with the message. I think the reason for the confusion is the word “Key” used in the error message. After I explained this to a developer who sent me the error he realize that it is about how we all interpret a same statement.

Following code will generate the error 1505.

-- Create Table
CREATE TABLE test (ID INT NOT NULL,
Col1 INT, Col2 VARCHAR(100))
GO
-- Populate Table
INSERT INTO test (ID, Col1, Col2)
SELECT 1, 1, 'First'
UNION ALL
SELECT 1, 2, 'Second' -- Duplicate ID col
UNION ALL
SELECT 3, 3, 'Third'
UNION ALL
SELECT 4, 4, 'Fourth'
GO

After creating a table, I am inserting same a key in the first column multiple times.

-- Now create PK on ID Col
ALTER TABLE test
ADD CONSTRAINT [PK_test]
PRIMARY KEY CLUSTERED
([ID] ASC)
GO

Now when I attempt to create a Primary Key on the column it gives us following error.

Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.test’ and the index name ‘PK_test’. The duplicate key value is (1).
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.

Fix / Workaround / Solution:

In SQL Server Primary Key can’t have duplicate records as well can’t have a column which is nullable. In our case, you can notice that we are creating a primary key on the column ID which contains duplicate values of 1. The only way to create primary key on this column is to delete the duplicate row which exists. If your business logic does not allow to delete the duplicate row, this means that your column is not a good candidate for the Primary Key and you will have to either select another column or use a composite Primary Key (where you use multiple columns).

On Separate note, here is the blog post with video which explains how you can delete the duplicate row from the table: Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Read Full Post »

SQL SERVER – How to ALTER CONSTRAINT

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 24, 2011 | 10 Comments »

After reading my earlier blog post SQL SERVER – Prevent Constraint to Allow NULL. I recently received question from user regarding how to alter the constraint.

No. We cannot alter the constraint, only thing we can do is drop and recreate it.

Here is the CREATE and DROP script.

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable (ID INT, Col1 INT, Col2 INT)
GO
-- Create Constraint on Col1
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col1
CHECK (Col1 > 0)
GO
-- Dropping Constraint on Col1
ALTER TABLE TestTable DROP CONSTRAINT CK_TestTable_Col1
GO
-- Clean up
USE MASTER
GO
ALTER DATABASE TestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE TestDB
GO

If you try to alter the constraint it will throw error.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Read Full Post »

SQL SERVER – Prevent Constraint to Allow NULL

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 21, 2011 | 10 Comments »

With naked eyes, we often spot the evident problems but the specific details are missed many a time. Something similar happened recently. One of the blog readers sent me an email asking about a bug in how CHECK CONSTRAINT works. He suggested that check constraint accepts NULL even though the rule is specified.

After looking at the whole script, I found out what he has done and how to prevent this type of error.

Let us first reproduce the script where the constraint allows NULL value in the column.

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable (ID INT, Col1 INT, Col2 INT)
GO
-- Create Constraint on Col1
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col1
CHECK (Col1 > 0)
GO
-- Insert will work fine
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,1,1)
GO
-- Insert will throw an error
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,0,1)
GO
-- Insert will work fine with NULL
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,NULL,1)
GO

Inserting the zero will throw an error.

Inserting NULL will not throw error.

The reality is that any constraint will prevent values that are evaluated as FALSE. When NULL is evaluated, it is not evaluated as FALSE but as UNKNOWN. Owing to the same reason, the constraint is allowing NULL to be inserted. If you want NULL not to be inserted, the constraint has to be created in such a way that NULL will not be allowed.

The following script is created on Col2 where NULL is not allowed. When NULL is attempted to inserted, it will throw Error 547 as displayed in the earlier image.

-- Add the Constraint on Col2
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col2
CHECK (Col2 > 0 AND Col2 IS NOT NULL)
GO
-- Insert will throw an error
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,1,NULL)
GO
-- Insert will work fine
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,1,1)
GO
-- Clean up
USE MASTER
GO
ALTER DATABASE TestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE TestDB
GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Read Full Post »

SQL SERVER – Disable Clustered Index and Data Insert

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 29, 2010 | 9 Comments »

Earlier today, I received following email.

“Dear Pinal,

We looked at your script and found out that in your script of disabling indexes, you have only included selected non-clustered index during the bulk insert and missed to disabled all the clustered index. Our DBA [name removed] has changed your script a bit and included all the clustered indexes. Since then our application is not working.

When DBA [name removed] tried to enable clustered indexes again he is facing error Incorrect syntax error.

We are in deep problem [word replaced]

[Removed Identity of organization and few unrelated stuff ]“

I have replied to my client and helped them fixed the problem. However, what really caught my attention was the concept of disabling clustered index. Let us try to learn a lesson from this experience.

In this case, there was no need to disable clustered index at all. I had done all the crucial work when I was called in to work on the tuning project. I removed unused indexes, created a few optimal indexes and wrote a script to disable selected high cost indexes when bulk insert operations (and the like) are performed. There was another script which rebuilds all the indexes as well. The solution worked until they included a clustered index in order to disable the script.

Clustered indexes are in fact original tables (or heap) which are physically ordered (any more things – not scope of this article) according to one or more keys (columns). When a clustered index is disabled, its data rows  cannot be accessed. This means that there will be no insertion process possible. On the other hand, when non-clustered indexes are disabled, all the data related to it are physically deleted, but the definition of the index is kept in the system.

Due to the same reason, even reorganization of the index is not possible until the clustered index (which was disabled) is rebuilt. Now, let us come to the second part of the question which is in regards to the receiving of the error when a clustered index is ‘enabled’. This is a very common question that I receive on the blog. (The following statement is written keeping the syntax of T-SQL in mind) Clustered indexes can be disabled but cannot be enabled again; they have to be rebuilt to become enabled. It is indeed a common thinking that something which we have ‘disabled’ can be ‘enabled’ but the syntax for this is ‘rebuild’. This issue has been explained here: SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’.

Let us go over this example where inserting the data is not possible when a clustered index is disabled.

USE AdventureWorks
GO
-- Create Table
CREATE TABLE [dbo].[TableName](
[ID] [int] NOT NULL,
[FirstCol] [varchar](50) NULL,
CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO
-- Create Nonclustered Index
CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName]
([FirstCol] ASC)
GO
-- Populate Table
INSERT INTO [dbo].[TableName]
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third'
GO
-- Disable Nonclustered Index
ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE
GO
-- Insert Data should work fine
INSERT INTO [dbo].[TableName]
SELECT 4, 'Fourth'
UNION ALL
SELECT 5, 'Fifth'
GO
-- Disable Clustered Index
ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE
GO
-- Insert Data will fail
INSERT INTO [dbo].[TableName]
SELECT 6, 'Sixth'
UNION ALL
SELECT 7, 'Seventh'
GO
/*
Error: Msg 8655, Level 16, State 1, Line 1
The query processor is unable to produce a plan because the index 'PK_TableName' on table or view 'TableName' is disabled.
*/
-- Reorganizing Index will also throw an error
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REORGANIZE
GO
/*
Error: Msg 1973, Level 16, State 1, Line 1
Cannot perform the specified operation on disabled index 'PK_TableName' on table 'dbo.TableName'.
*/
-- Rebuliding should work fine
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD
GO
-- Insert Data should work fine
INSERT INTO [dbo].[TableName]
SELECT 6, 'Sixth'
UNION ALL
SELECT 7, 'Seventh'
GO
-- Clean Up
DROP TABLE [dbo].[TableName]
GO

I hope this example is clear enough. There were a few additional posts I had written years ago, and they are as follows:

SQL SERVER – Enable and Disable Index Non Clustered Indexes Using T-SQL

SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Read Full Post »

Older Posts »