modify column size changes foreign key relationship

Question

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

Answer 1

This is because you are scripting a Table only and only those dependencies are scripted which do not required any missing object. Technically the Foreign Key constraint could be added if you select to script both "Proc" and "Doc_Review" Tables together (which is not possible due to UI constraints).

What you can do is right click on the Database in the SSMS and go to "Task > Generate Scripts" and select to script the tables you want (in your case "Proc" and "Doc_Review"). Once you are done generating the script, you will have all FK constraints also added to the script.

Hope this helps-

---

Please mark the post as answered if it answers your question

Answer 2

Your script, as posted, is incomplete at best.  It should not run "fine".  The script creates a PK in Proc using the column "Process_Transaction_ID", which does not exist in the table.  It attempts to create a FK from Proc to Doc_Review on Doc_ID - another column that does not exist in Proc. It checks a constraint named "FK_Process_Transaction_Documentation_Review_6" on Proc - which does not exist according to the script.  The last "add constraint" statement is syntactically incorrect - look closely at the name - and the following check constraint statement uses a different name.