modify constratints

12 May 2005 10:58 PM

AM

Hi all

In Table1 I have prmary key constraint and it is refernced by some column
as a foreign key in Table2

Now I want to modify my constraint by TSQL in primary key table ,
How to do it?

For ex
in primary key table

ALTER TABLE [dbo].[table1]
add
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[col1]
) ON [PRIMARY]
GO

in foreign key table

ALTER TABLE [dbo].[table2] ADD
CONSTRAINT [FK_table2_table1] FOREIGN KEY
(
[colA]
) REFERENCES [dbo].[table1] (
[col1]
)
GO

Now I want to change constraint PK_Table1 in primary key table to
NonClustered by TSQL Code. How to do it ?
I can not drop the constraint in primary key table since it is referenced by
other table so how to modify it to Nonclustered?

--
Am

13 May 2005 2:40 AM

Louis Davidson

drop the foreign key constraints and then recreate them.

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP

Show quote

"AM" <anonym***@developersdex.com> wrote in message
news:OUDysV0VFHA.3540@TK2MSFTNGP15.phx.gbl...
> Hi all
>
> In Table1 I have prmary key constraint and it is refernced by some column
> as a foreign key in Table2
>
> Now I want to modify my constraint by TSQL in primary key table ,
> How to do it?
>
> For ex
> in primary key table
>
> ALTER TABLE [dbo].[table1]
> add
> CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
> (
> [col1]
> ) ON [PRIMARY]
> GO
>
> in foreign key table
>
> ALTER TABLE [dbo].[table2] ADD
> CONSTRAINT [FK_table2_table1] FOREIGN KEY
> (
> [colA]
> ) REFERENCES [dbo].[table1] (
> [col1]
> )
> GO
>
> Now I want to change constraint PK_Table1 in primary key table to
> NonClustered by TSQL Code. How to do it ?
> I can not drop the constraint in primary key table since it is referenced
> by
> other table so how to modify it to Nonclustered?
>
>
>
> --
> Am
>
>

13 May 2005 4:49 PM

AM

But If I change the primary key constrarint from EM then I don't have to
drop foreign key constraint.

Same can not be done by code?

Thanks
AM

"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:uNTnUU2VFHA.2660@TK2MSFTNGP10.phx.gbl...
> drop the foreign key constraints and then recreate them.
>
> --
> --------------------------------------------------------------------------

--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "AM" <anonym***@developersdex.com> wrote in message
> news:OUDysV0VFHA.3540@TK2MSFTNGP15.phx.gbl...
> > Hi all
> >
> > In Table1 I have prmary key constraint and it is refernced by some
column
> > as a foreign key in Table2
> >
> > Now I want to modify my constraint by TSQL in primary key table ,
> > How to do it?
> >
> > For ex
> > in primary key table
> >
> > ALTER TABLE [dbo].[table1]
> > add
> > CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
> > (
> > [col1]
> > ) ON [PRIMARY]
> > GO
> >
> > in foreign key table
> >
> > ALTER TABLE [dbo].[table2] ADD
> > CONSTRAINT [FK_table2_table1] FOREIGN KEY
> > (
> > [colA]
> > ) REFERENCES [dbo].[table1] (
> > [col1]
> > )
> > GO
> >
> > Now I want to change constraint PK_Table1 in primary key table to
> > NonClustered by TSQL Code. How to do it ?
> > I can not drop the constraint in primary key table since it is
referenced
> > by
> > other table so how to modify it to Nonclustered?
> >
> >
> >
> > --
> > Am
> >
> >
>
>

13 May 2005 5:26 PM

Raymond D'Anjou

Behind the scenes, EM drops and recreates foreign key constraints.
In EM, clique the "save script" icon after you have made the change and you
will see.

Show quote

"AM" <anonym***@developersdex.com> wrote in message
news:uzH99z9VFHA.3488@TK2MSFTNGP10.phx.gbl...
> But If I change the primary key constrarint from EM then I don't have to
> drop foreign key constraint.
>
> Same can not be done by code?
>
> Thanks
> AM
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:uNTnUU2VFHA.2660@TK2MSFTNGP10.phx.gbl...
>> drop the foreign key constraints and then recreate them.
>>
>> --
>> --------------------------------------------------------------------------
> --
>> Louis Davidson - http://spaces.msn.com/members/drsql/
>> SQL Server MVP
>>
>>
>> "AM" <anonym***@developersdex.com> wrote in message
>> news:OUDysV0VFHA.3540@TK2MSFTNGP15.phx.gbl...
>> > Hi all
>> >
>> > In Table1 I have prmary key constraint and it is refernced by some
> column
>> > as a foreign key in Table2
>> >
>> > Now I want to modify my constraint by TSQL in primary key table ,
>> > How to do it?
>> >
>> > For ex
>> > in primary key table
>> >
>> > ALTER TABLE [dbo].[table1]
>> > add
>> > CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
>> > (
>> > [col1]
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > in foreign key table
>> >
>> > ALTER TABLE [dbo].[table2] ADD
>> > CONSTRAINT [FK_table2_table1] FOREIGN KEY
>> > (
>> > [colA]
>> > ) REFERENCES [dbo].[table1] (
>> > [col1]
>> > )
>> > GO
>> >
>> > Now I want to change constraint PK_Table1 in primary key table to
>> > NonClustered by TSQL Code. How to do it ?
>> > I can not drop the constraint in primary key table since it is
> referenced
>> > by
>> > other table so how to modify it to Nonclustered?
>> >
>> >
>> >
>> > --
>> > Am
>> >
>> >
>>
>>
>
>