August 6, 2012 at 9:04 am
In a sql server 2008 r2 database, I am changing the size of a column called NOTES from varchar(500) to varchar(max). When this occurs, I lose the foregin key and relationships to other tables.
Basically when I run the script below in sql server management studio 2008 r2, everything runs fine. However when I right click on the 'proc' table in sql server management studio and select, 'script table as'-->create to-->new query edit window, I lose the relationship to the other tables.
Thus can you tell me if I am really losing the relationship to the tables and why this is occuring? Is the field called 'notes' used as part of the index?
If I am not losing the relationship of the 'proc' table to the other tables, can you tell me why sql server studio 2008 r2 is not showing the relationship to the other tables?
The following is the script I am referring to:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Proc](
[Proc_ID] [int] IDENTITY(1,1) NOT NULL,
[Excep_ID] [int] NULL,
[NOTES] [varchar](max) NULL
CONSTRAINT [PK_Proc] PRIMARY KEY CLUSTERED
(
[Process_Transaction_ID] 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].[Proc] WITH CHECK ADD CONSTRAINT [FK_Proc_Doc] FOREIGN KEY([Doc_ID])
REFERENCES [dbo].[Doc_Review] ([Doc_ID])
GO
ALTER TABLE [dbo].[Proc] CHECK CONSTRAINT [FK_Process_Transaction_Documentation_Review_6]
GO
ALTER TABLE [dbo].[Proc] WITH CHECK ADD CONSTRAINT [FK_Proc_Enroll FOREIGN KEY([Enroll_ID])
REFERENCES [dbo].[Enrol_Mech] ([Enrol_Mech_ID])
GO
ALTER TABLE [dbo].[Proc] CHECK CONSTRAINT [FK_Proc_Enroll_Mech]
GO
August 6, 2012 at 9:11 am
I must be missing something, or this script must be missing something.
First, the primary key constraint is on a column that's not in the table definition.
Same for each of the FK constraints - they're from columns that aren't in the table definition.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 9:12 am
There are foreign keys referenced in the below script, SSMS just scripts them as ALTERS rather than in-line with the table definition. For e.g. this is a Foreign Key definition:
ALTER TABLE [dbo].[Proc] WITH CHECK ADD CONSTRAINT [FK_Proc_Enroll FOREIGN KEY([Enroll_ID])
REFERENCES [dbo].[Enrol_Mech] ([Enrol_Mech_ID])
GO
However, you've shown us a script that re-creates a table from scratch (presumably losing all your existing data). If you just want to change the data type to a new type that's a subset of the existing data (such as changing from varchar(n) to varchar(max)), then you should use the ALTER TABLE...ALTER COLUMN syntax. E.g.:
ALTER TABLE [PROC]
ALTER COLUMN Notes VARCHAR(MAX) NULL
August 6, 2012 at 9:13 am
Thought of a possibility. Are you modifying the column by dropping and re-creating the table, or by using the table designer GUI, or by issuing a simple Alter Table script?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 9:42 am
I am just giving you an example of the sql that is generated by the ssms. if there is sql problems, I just showed you an example, since I am not suppose to show the real data names.
Thus can you just answer my question?
August 6, 2012 at 9:46 am
wendy elizabeth (8/6/2012)
I am just giving you an example of the sql that is generated by the ssms. if there is sql problems, I just showed you an example, since I am not suppose to show the real data names.Thus can you just answer my question?
Nope.
There is no way to tell what's wrong with a database script that I can't look at.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2012 at 9:52 am
wendy elizabeth (8/6/2012)
Thus can you just answer my question?
Well, we've both tried to do so, but your question isn't clear. You've stated you're trying to alter a column, but you've posted a CREATE TABLE script instead. You've stated that the relationships to other tables have been 'lost', but there are relationships to other tables in the script.
Until you provide more information, I'm not sure how anyone can help further...
August 6, 2012 at 10:16 am
I found out what my problem is. Thanks everyone!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy