modify constratints

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?
Amdrop the foreign key constraints and then recreate them.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"AM" <anonymous@.examnotes.net> 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
>|||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" <anonymous@.examnotes.net> wrote in message
> news:OUDysV0VFHA.3540@.TK2MSFTNGP15.phx.gbl...
column
referenced
>|||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.
"AM" <anonymous@.examnotes.net> 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...
> --
> column
> referenced
>

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.

sql

Model for Report Builder

I made a model for report builder and I am limited to selecting from 2 tables I have one main table table1 with a PK to FK with table2 - table12. I am limited to only going 2 levels deep is this right? How can I correct this or work around it. I tried building a view, but have never had to do so with so many joins it this possible or do I have to break things up?

I'm not sure I understand the limitation you are hitting. Are you seeing problems during model design or in Report Builder after you publish the model?

|||I have one main table with PK and several tables with a FK to the main table. After building my model I am limited to select only from the main table and one other table, the ths most I can grab from is 2 tables.|||

I'm not aware of any existing issues like this. Here's a few things you could double check:

- The entities in the model that correspond to the "other" tables all have IsLookup=false and Hidden=false

- The roles in the model from the main entity to the "other" entities all have Cardinality=OptionalMany

- I assume you are using Model Designer to build the model against a SQL database, not generating the model from an Analysis Services cube.