Table with Cluster Key and Primary Keys - Update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table with Cluster Key and Primary Keys - Update

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2015-02-16 : 20:57:11

I have this table that I thought I could just do a normal update to because I need to add a state and ID. It isn't that simple.

How do I add a state to a table that is already created?

Here is the behind the scenes. I have never had to deal with something like this.

This is what it looks like

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[RegionStates](
[RegionID] [smallint] NOT NULL,
[State] [char](2) NOT NULL,
CONSTRAINT [PK_RegionStates] PRIMARY KEY CLUSTERED
(
[RegionID] ASC,
[State] 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_PADDING OFF
GO

ALTER TABLE [dbo].[RegionStates] WITH CHECK ADD CONSTRAINT [FK_RegionStates_Regions] FOREIGN KEY([RegionID])
REFERENCES [dbo].[Regions] ([ID])
GO

ALTER TABLE [dbo].[RegionStates] CHECK CONSTRAINT [FK_RegionStates_Regions]
GO

ALTER TABLE [dbo].[RegionStates] WITH CHECK ADD CONSTRAINT [FK_RegionStates_USStates] FOREIGN KEY([State])
REFERENCES [dbo].[USStates] ([StateCode])
GO

ALTER TABLE [dbo].[RegionStates] CHECK CONSTRAINT [FK_RegionStates_USStates]
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-16 : 21:00:46

Here's an example: ALTER TABLE Table1 ADD Column12 varchar(50) NULL

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources