Archive

Posts Tagged ‘ALTER TABLE’

‘NOCHECK’ vs ‘WITH NOCHECK’… while creating disabled Foreign Key (FK) constraint

March 14, 2012 2 comments

I dicussed about disabling FK constraints in one of my previous post to UPDATE/DELETE records to avoid some conflicts, check this [link] for more info.

There is an option while adding a Constraint to a table:

-- Syntax:
ALTER TABLE TABLE_NAME WITH NOCHECK 
ADD CONSTRAINT FK_CONSTRAINT_NAME FOREIGN KEY(COL_NAME_ID)
REFERENCES OTHER_REF_TABLE (OTHER_TABLE_COL_NAME_ID)

But this doesn’t disable the FK constraint, instead it creates the FK constraint without checking existing records.

You will need to issue a separate ALTER TABLE statement to disable the FK constraint:

-- Syntax:
ALTER TABLE TABLE_NAME NOCHECK CONSTRAINT FK_CONSTRAINT_NAME

So, what’s the difference here when we apply ‘WITH NOCHECK’ vs ‘NOCHECK’?

Let’s check this by taking the [Sales].[SalesOrderDetail] table from AdventureWorks database:

USE [AdventureWorks2008R2]
GO

-- Here I just created the script of [Sales].[SalesOrderDetail] table and stripped off the unwanted parts and changed the table name to [Sales].[SalesOrderDetailTemp]
CREATE TABLE [Sales].[SalesOrderDetailTemp](
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[SpecialOfferID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetailTemp_SalesOrderID_SalesOrderDetailID] 
 PRIMARY KEY CLUSTERED (
	[SalesOrderID] ASC,
	[SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Let's create a FK constraint with 'WITH NOCHECK' option
ALTER TABLE [Sales].[SalesOrderDetailTemp]  
	WITH NOCHECK 
	ADD CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
	REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE NO ACTION
GO

Now, go to Object Explorer check the Table -> Keys. Right click on the FK and select Modify. Check the following option on the FK Relationship popup box:
– General -> Check existing data on Creation or Re-Enabling = No
– Table Designer -> Enforce foreign key constraint = Yes

This shows that the FK constriant is still enabled but it didn’t validated the existing records while creating the FK.

Now to really disable the FK we will just have to use the NOCHECK option without using the WITH keyword:

ALTER TABLE [Sales].[SalesOrderDetailTemp]  
	NOCHECK CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID]
GO

Now, check again the Table -> Keys in Object Explorer. You will see the second option ‘Enforce foreign key constraint’ has changed to ‘No’ in the FK Relationship popup box.

-- Final cleanup
DROP TABLE [Sales].[SalesOrderDetailTemp]
GO

So, finally we saw the difference in using the NOCHECK option by using ‘WITH’ and without ‘WITH’ keyword.
And you cannot directly add a Disabled Constraint to a table. You will need to create the FK Constraint first then only you can disable it by using another ALTER TABLE statement.

SQL Basics – ADD or DROP (remove) Columns in a Table in SQL Server

June 25, 2009 2 comments

In my [previous post] we saw how to create a new Database and a new Table.
 

Today we will see how to ADD a new column to a table and DROP an existing column from a table.

To ADD or DROP columns you have to use the ALTER TABLE statement, that is also used to change the datatype of existing columns. Please check the video and the Scripts below to check and learn about both of these actions.

Please note: while adding a new column by using ALTER TABLE ADD statement the columns are always added at the end of the table. But if you want to add the column at a specific position use the Table Designer in SSSMS. Adding column in between is not recommended as it may do data movement by introducing an intermediate temporary table. Thus it is advised to add the columns at the end only and while Querying it you can use the specific column in the middle of the SELECT statement.
 

Check video on how to ADD and DROP columns in a Table:

–> SQL Script used in the above demo:

USE [Demo]
GO

-- create a sample table:

CREATE TABLE [dbo].[Employee](
	[EmployeeID]	int				NOT NULL,
	[EmployeeName]	nvarchar(100)	NOT NULL,
	[Gender]		nchar(1)		NULL,
	[DOB]			datetime		NULL,
	[DOJ]			datetime		NULL,
	[DeptID]		int				NULL
)

-- insert some sample/test records:
INSERT INTO [dbo].[Employee]
VALUES (1, 'MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee]
VALUES (2, 'JHON K', 'M', NULL, '2010-01-01', NULL)

INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName])
VALUES (3, 'Brock H')
GO

SELECT * FROM [dbo].[Employee]
GO


-- Add a new COLUMN:

ALTER TABLE [dbo].[Employee]
ADD MgrID INT
GO

SELECT * FROM [dbo].[Employee]
GO


-- Add column at specific location:
BEGIN TRANSACTION
GO

CREATE TABLE dbo.Tmp_Employee
	(
	EmployeeID int NOT NULL,
	EmployeeName nvarchar(100) NOT NULL,
	Gender nchar(1) NULL,
	FathersName nvarchar(100) NULL,
	DOB datetime NULL,
	DOJ datetime NULL,
	DeptID int NULL,
	MgrID int NULL
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.Tmp_Employee SET (LOCK_ESCALATION = TABLE)
GO

IF EXISTS(SELECT * FROM dbo.Employee)
	 EXEC('INSERT INTO dbo.Tmp_Employee (EmployeeID, EmployeeName, Gender, DOB, DOJ, DeptID, MgrID)
		SELECT EmployeeID, EmployeeName, Gender, DOB, DOJ, DeptID, MgrID FROM dbo.Employee WITH (HOLDLOCK TABLOCKX)')
GO

DROP TABLE dbo.Employee
GO

EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT' 
GO

COMMIT


SELECT EmployeeID, EmployeeName, Gender, MgrID, FathersName, DOB, DOJ, DeptID 
FROM [dbo].[Employee]
GO


-- Drop an existing Column:

ALTER TABLE [dbo].[Employee]
DROP Column FathersName
GO


-- Adding Column with NOT NULL values

ALTER TABLE [dbo].[Employee]
ADD Address VARCHAR(100) NOT NULL
GO
-- error
/*
Msg 4901, Level 16, State 1, Line 38
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'Address' cannot be added to non-empty table 'Employee' because it does not satisfy these conditions.
*/

ALTER TABLE [dbo].[Employee]
ADD Address VARCHAR(100) NOT NULL DEFAULT ('N/A')
GO

SELECT * FROM [dbo].[Employee]
GO

ALTER TABLE [dbo].[Employee]
DROP COLUMN Address
GO
-- error
/*
Msg 5074, Level 16, State 1, Line 55
The object 'DF__Employee__Addres__xxxxxxxx' is dependent on column 'Address'.
Msg 4922, Level 16, State 9, Line 55
ALTER TABLE DROP COLUMN Address failed because one or more objects access this column.
*/

ALTER TABLE [dbo].[Employee] 
DROP CONSTRAINT [DF__Employee__Addres__3C69FB99]
GO

ALTER TABLE [dbo].[Employee]
DROP COLUMN Address
GO


-- Final Cleanup
DROP TABLE [dbo].[Employee]
GO

 

Check my [next post] on how to Rename & change datatype of columns in a table.