SQL Introduction Continued

Following on from my previous article on an introduction SQL this post covers some scripts I had to write to alter a table structure.

Being able to script these changes obviously makes it easier to move changes between development, Q/A and production.

As I was in a development environment I was able to clear the table prior to performing the column removal. All this is possible through the SQL Management studio, but using T-SQL is a powerful skill to have in a developers toolkit.
Let’s say we have a table with relationships to other tables and we want to remove column_5 from the table. In this case column_5 is part of the primary key.

sql-relationships

Use these scripts to create the tables needed.

CREATE TABLE [dbo].[table3]( [column_3] [int] NOT NULL, CONSTRAINT [PK_table3] PRIMARY KEY CLUSTERED ( [column_3] 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
CREATE TABLE [dbo].[table2]( [column_2] [int] NOT NULL, CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ( [column_2] 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON 
GO
CREATE TABLE [dbo].[table1]( [column_1] [int] NOT NULL, [column_2] [int] NOT NULL, [column_3] [int] NOT NULL, [column_4] [int] NOT NULL, [column_5] [int] NOT NULL, CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ( [column_1] ASC, [column_2] ASC, [column_3] ASC, [column_4] ASC, [column_5] 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

 

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [FK_table1_table2] FOREIGN KEY([column_2])REFERENCES [dbo].[table2] ([column_2])

GO
ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_table2]

GO


ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [FK_table1_table3] FOREIGN KEY([column_3])REFERENCES [dbo].[table3] ([column_3])

GO


ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_table3]

GO

 

Clear the data from the table

DELETE FROM dbo.table1

Let’s try and drop the column straight away.

ALTER TABLE dbo.table1 DROP COLUMN column_5

The object ‘PK_table1’ is dependent on column ‘column_5’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN column_5 failed because one or more objects access this column.

This is because column_5 is currently part of the primary key.

Note: If column_5 was part of a foreign key then you’d need to remove that relationship by using the following statement. In our case we can move straight onto removing the primary key which column_5 is part of.

Remove foreign key constraints (if any exist)

ALTER TABLE dbo.table1 DROP CONSTRAINT FK_table1_table2;
ALTER TABLE dbo.table1 DROP CONSTRAINT FK_table1_table3;

Remove primary key constraint

ALTER TABLE dbo.table1 DROP CONSTRAINT PK_table1;

Drop column_5 from the table

ALTER TABLE dbo.table1 DROP COLUMN column_5

Re-create the foreign key constraints

Now we can add back in our foreign key constraints

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [FK_table1_table2] FOREIGN KEY([column_2])REFERENCES [dbo].[table2] ([column_2])

GO
ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_table2]

GO
ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [FK_table1_table3] FOREIGN KEY([column_3])REFERENCES [dbo].[table3] ([column_3])

GO
ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_table3]

GO

 

Now let’s add the primary key back onto the table.


If you refresh the table in SQL Management studio you will notice that column_5 has been removed, the foreign key constraints have been applied again and the primary key is in place (excluding column_5).

This is again simple example using T-SQL, but it shows the control and power being able to write these provides.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s