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
>

Modification of the primary key !!

Hi

I have binded a DataGradView (DGV) with a Dataset (DS) , i execute and modify on the DataGradView the primary key (IDObj) and also the two other columns, by clicking on the UpDate button i call the function UpDateTable :

public void UpdateTable(string nameTable)
{
// con : ma connection OLE à un fichier Access 2003
// DS : dataSet
// DAdp : Dataadapter
// nameTable : nom de ma table
OleDbCommand comdUPDATE;
string CommandText = "UPDATE " + nameTable + " SET IDObj=@.IDObj ,NameParent=@.NameParent,TypeObj=@.TypeObj WHERE IDObj=@.IDObj";
comdUPDATE=DAdp.UpdateCommand = new OleDbCommand(CommandText, con);
// IDObj : cl principale
comdUPDATE.Parameters.Add(new OleDbParameter("@.IDObj", OleDbType.VarChar, 50));
comdUPDATE.Parameters.Add(new OleDbParameter("@.NameParent", OleDbType.VarChar, 50));
comdUPDATE.Parameters.Add(new OleDbParameter("@.TypeObj", OleDbType.VarChar, 50));
comdUPDATE.Parameters["@.IDObj"].SourceVersion= DataRowVersion.Original; //!!!
comdUPDATE.Parameters["@.NameParent"].SourceVersion= DataRowVersion.Current;
comdUPDATE.Parameters["@.TypeObj"].SourceVersion= DataRowVersion.Current;

comdUPDATE.Parameters["@.IDObj"].SourceColumn = "IDObj";
comdUPDATE.Parameters["@.NameParent"].SourceColumn = "NameParent";
comdUPDATE.Parameters["@.TypeObj"].SourceColumn = "TypeObj";
DataSet modifiedDS = DS.GetChanges(DataRowState.Modified);

try
{
con.Open();
DAdp.Update(modifiedDS.Tables[nameTable]);
DS.Clear();
DAdp.Fill(DS, nameTable);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
finally { con.Close(); }

}

But i have this exception : Concurrency violation : the update command affected 0 of the expected 1 records.

how is it possible to modify a primary key witch is the reference of that modified row?

Thanks for help

The more inportant question is WHY would you ever want to modify a primary key?

Primary keys are commonly used to create relationships between data. If the Primary Key was altered, the relationship would break.

The concurrency violation is because you are attempting to create two rows with the same primary key value -and that is not allowed.

|||

I know that each row has to be identified with that primary key it's like its coordinates.

but sometimes the identification of an object (equipments, tasks..) is wrong and need to be corrected.

is there i way to do it?

|||

Hi,

You are going to alter the database design. It better if you make amendment at database level.

Cheers!!

|||

Yes, you just update the table and set the Primary Key column equal to the new value.

However, if that new value already exists in the table, you will get an error -like the error you are currently getting. There cannot be two rows in the table with the exact same primary key value.

However, in your query, there is a problem:

" SET IDObj=@.IDObj ,NameParent=@.NameParent,TypeObj=@.TypeObj WHERE IDObj=@.IDObj"[/quote]

This is written like this: SET A = B WHERE A = B

I hope you can see the problem. You need two parameters, one for the old value to use in the WHERE clause, and another for the new value assignment. Perhaps more like:

Code Snippet

UPDATE MyTable
SET
IDObj = @.NewIDObj,
NameParent = @.NameParent,
TypeObj = @.TypeObj
WHERE IDObj = @.OldIDObj

|||

I'll trey this :

MyUpdateCommd.Parameters["@.OldIDObj"].SourceVersion =DataRowVersion.Original;

and let :

MyUpdateCommd.Parameters["@.NewIDObj"].SourceVersion =DataRowVersion.Current;

is it that?

|||That looks like it should work.|||

Hi Gays

always same exception.

A) what i have :

i binded i DataGradeView with Dataset.

i try to modify value of any columns.

the Update command failed and exception rised.

B) What i code :

string CommandText =
"UPDATE " + nameTable +
" SET IDObj=@.IDObj , NameParent=@.NameParent, TypeObj=@.TypeObj"+
" WHERE IDObj=@.IDObjOrig ";

I indicate the VersionSource wich is "Current" for all parameters exept for @.IDObjOrig that is "Original"

i did not use the value property of parameters because i modify by editing on Datagradview then i call the UpDate method of the DataAdapter by passing my dataset and the name of my table.

? exception......I don't understand ..

modeling a table with FK

I am having a problem when modeling a Foreign Key in an "Operations" table. This table holds all information on customers ′s applications and withdrawals.

Here is the structure:

CustomerID int, SourceID int, Value decimal (16,2), OperationDate datetime

Well the problem is that SourceID sometimes might be NULL depending on how the record was inserted. So its kind of cumbersome to define it as an FK, since it can be null...To get things worse, this SourceID might point to more than 1 table (depending on the CustomerType it will point to SourceA table or SourceB table)...

How should this be modeled?

What is sourceId? It sounds like you need to do a bit more normalization here and have a source table.

source
sourceId int
customerType
<other source bits>

Then your other tables like SourceA and SourceB reference this table, as well as the operations table:

create table operations
(
...I assume you have a key other than these columns,
customerId int
sourceId int null references source(sourceId)
)

Then the source will either be sourceA or sourceB depending on the customerType (assuming you are defining the source for a given customerType.) I am not sure that I am making sense, but this sounds like what I am getting from your post. If not, can you give your current table structures and a bit more about usage?