.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

need to change primary key to a composite primary key.

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :ASP.Net

This is a daunting task, because I have never done it before and don't want to seriously break anything. Basically I have a table with on primary key. I need to change the table to have a composite primary key. So do I first remove the primary key constraint and then proceed from their? I can do it through the GUI. It would be nice to know how to do it through sql though. Can anyone give me some guidance/help?

View Complete Post

More Related Resource Links

foreigh key for composite primary key



i created one table in stock management which contatins cascade primary key like below

Create table TblStockLocation
LocationName varchar(50),
RacNumber int(50),
constraint pkForStock primary key(LocationName,RacNo))

how I can reference this primary key in stock table

create table stock(productid tinyint primary key,

pname varchar(20),

productLocation ? data type references TblStockLocation(?)



is this is currect way please suggest



foreign key to reference composite primary key


Hello all!

I am new to tsql so please be kind if this is bad idea.  I am using visual studio, thus why everything is split up.
I am writing software that allows the user to "turn on" customizable tables to use.  each table is the same.  so for brevity in the database i made a single table 'User'.

CREATE TABLE [dbo].[User]
 U_Table    int     NOT NULL,
 U_ID     varchar(50)   NOT NULL


ALTER TABLE [dbo].[User]

the primary key is made up of a table id (which user-customizable table) and the record id
now i have a transaction table that holds the id's used for each user customizable table

CREATE TABLE [dbo].[Transaction]
TRAN_ID      int     NOT NULL,
TRAN_User_01    varchar(50)   NULL,
 TRAN_User_02    varchar(50)   NULL,
 TRAN_User_03    varchar(50)   NULL,
 TRAN_User_04    varchar(50)   NULL

My question is, i want to add a foreign key relationshi

should i make that composite key into a 1-col primary key



Hi all, what i have now is this:

create table House(Id int identity(1,1)primary key, ..)

create table People(HouseId int not null references House, Id int not null, ..primary key(HouseId, Id))

so basically the Id column of table People is supposed to be auto-increment.

i've read this thread http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/792a5b82-2d13-46bd-8650-a75a74ea222f but the solution is not concurrent (i think, or am i wrong)

should i recreate the People table and identify each "People" with a single auto-incrementing primary key, or should i create a stored procedure that maintains the calculation of the Id column in a concurrently safe way to "fake" autoincrementing?


ps: human and people tables is just a simulation to voice the problem more easily instead of using my actual tables

Surrogate vs Natural Primary Keys - Data Modeling Mistake 2 of 10

In case you're new to the series I've compiled a list of ten data modeling mistakes that I see over and over that I'm tackling one by one. I'll be speaking about these topics at the upcoming IASA conference in October, so I'm hoping to generate some discussion to at least confirm I have well founded arguments.

The last post in this series Referential Integrity was probably less controversial than this one. After all, who can argue against enforcing referential integrity? But as obvious as surrogate keys may be to some, there is a good deal of diversity of opinion as evidenced by the fact that people continue to not use them.

Passing Primary Key to User Control to Display Record Detail


I'm building a user control to display detail information on a parent record. The user control will be displayed via an Ajax Modal Popup. The user control works and is displayed when expected. The problem that I'm having is that I'm not able to pass in the primary key for the parent. The control is displayed via the _ItemCommand event of a Repeater. In the markup for the user control, I explicity set the ContactId to 0 to avoid an error when the control loads (its still underdevelopment, eventually that I'll check if the value is valid).

I was assuming that I could change the property in the _ItemCommand event, but obviously its not working.

In the Page_Load event this code successfully sets the ContactId property of the control. The _ItemCommand event appears to set the value. However when I set a test label on the user control to the Me.ContactId, its showing '0' as if it was never passed in.

        UserControlContactDetail.ContactId = "1462"
        test.Text = UserControlContactDetail.ContactId

    Protected Sub RepeaterDirectory_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.RepeaterCommandEventArgs) Handles RepeaterDirectory.ItemCommand

Protected Sub RepeaterDirectory_ItemCommand(By

Using Ref as an increasing primary key value


Hi. I need to set the Ref value as an increasing value starting with the number 2010 ... it will be a 12 digits characters.

I tried using identity but it doesn't work. Could anyone advice on how should one could insert an increment of 10 to the ref value? In case, my explaination is not clear, below is an example.

For an example:

Record 1: ref - 201000000123

Record 2: ref - 201000000133

Record 3: ref - 201000000143



Primary Key In Share Point 2007/ 2010


Can I create 3 columns Primary key in a list using the MOSS 2010 or 2007

Actually My requirement  is

I collect some information by Country,State,Dist wise Temperature


1.  India-Andraprades-Hyderabad-Temp-30

2.  India-Andraprades-KarimNagar-Temp-30

here first 2 column is Same but 3rd column is different that's I required 3 column is Primary Key


Any one help me.



Using table with no primary key in CTP4



Does anyone knows if CTP 4.0 supports table with no primary key. I know Entity framework does.

Can't perform Create, Update or Delete operations on 'Table(outbox)' because it has no primary ke



I'm a beginner in asp.net and I have some problem inserting data into sql server 2005 with linq.


my code is : 


        Dim Result As New SMS.SendResult
        If Result.status = 0 Then
            Label1.Text = "Your Message Sent Successfuly"
            Dim db As New IGSSDataContext
            Dim out As New outbox With {.ClientID = intRandomNumber, .From = User.Identity.Name, .TO = TextBox1.TabIndex, _
            .Time = Now, .Message = Message}
            out.ID = (ID)
            TextBox1.Text = ""

How to get all the foreign key references for a primary key

Hi ,  I would like to know all the foriegn key references in the db for a table's primary key..How can i get that? Thanks sunil m datla

Creating Sequence as Primary Key

Hi. I need to have a running number of 12 digits to be acted as ref number. it needs to be increment by 10.May I know how can I do that? Eg: Record 1  000000000001      Record 2 0000000000010

Primary Key Constraint Violation on Update

When UPDATING a single column (NOT the primary key column) of a transactionally replicated table, within MS SQL Server Management Studio, the replication monitor gives  Violation of PRIMARY KEY constraint 'PK_my_table. Cannot insert duplicate key in object 'my_table'. Statement Delivery is via the standard SCALL of dbo.sp_MSupd_my_table. The problem correlates with Statement Delivery of DELETE operations. That is, the problem exists if I specify "Do not replicate Delete Statements" and goes away if I specify standard behaviour for delete i.e a call on dbo.sp_MSdel_My_Table dbo.sp_MSupd_my_table does not appear to invoke deletion in any way and Im not updating the primary key value Any ideas Henrietta

Error Message: The referenced table must have a primary or candidate key. [ FK Name = FK_tblA_tblB ]

An Entity Data Model is used to generate the script to create  a SQL CE database.  It creates all of the 10+ tables and their primary keys without any problem. A lot of foreign key creations generate errors like the following: SQL Execution Error. Executed SQL statement ALTER TABLE [tblA] ADD CONSTRAINT [FK_tblA_tblB] FOREIGN KEY([A], [B], [C]) REFERENCES [tblB]([A], [BBB], [C]) Error Source: SQL Server Compact ADO.NET Data Provider Error Message: The referenced table must have a primary or candidate key. [ FK Name = FK_tblA_tblB ] The primary keys are there and correct. Could anyone offer some tips on how to figure out what is wrong?hz

Gridview Selected Value is Not a Primary Key

Hello,  I have 2 GridViews.  One is working perfectly.  What I would like to have happen is when a row is selected on GridView1 (with Primary key of OrderNumber), the matching records from the field in GridView1 ClientID to show up in GridView2. <asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="\\sjfileserver\data\database\S.J. Rollins Technologies Information Center.mdb" SelectCommand="SELECT [ClientID], [OrderNumber], [Field1] FROM [ORDERS] WHERE ([ClientID] = ?)"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ClientID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:AccessDataSource> <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID" DataSourceID="AccessDataSource2"> <Columns> <asp:BoundField DataField="CustomerID" HeaderText="ClientID" SortExpression="ClientID" /> <asp:BoundField DataField="OrderNumber" HeaderText="OrderNumber"

How can you use Linq to SQL to get the primary key of a record you've just inserted?

Hi,I'm inserting data into a database with Linq to SQL and would like to access the primary key of that record WITHOUT doing a query for the values I've already captured.Here is my insert startment: Dim page As New pageData With {.pageTitle = txtPageTitle.Text, .pageDescription = txtPageDescription.Text, .pageContent = HttpUtility.HtmlEncode(txtPageContent.Text) }There is a value in the table that is an auto increment integer.Rather than doing a query for the pagetitle, description and content I'd like to just access the primary key that I would have just generated by performing the insert.Is this possible or would I have to perform the query as I've just mentioned to retrieve it?

LINQ - Can't perform Create, Update or Delete operations on because it has no primary key.

Hi, not sure if I'm posting this thread in the right section. anyway, I''m trying to do the update/insert/delete using LINQ but the underlying table does not a primary key. The table consists of composite ket (e.g. fieldA & FieldB make a primary key). At the moment, I have LINQ complaning about not having a single primary key (not sure what LINQ does it - not making any sense at all). did anyone ever come across this issue and rectified it?  thanks

Two Primary key for table???

i HAVE FOLLOWING SCRIPT TO CREATE TABLE. create table Grade_report (STUDENT_NUMBER SMALLINT NOT NULL, SECTION_ID SMALLINT NOT NULL, GRADE CHAR(1), CONSTRAINT stno_secid PRIMARY KEY (STUDENT_NUMBER, SECTION_ID)) tHE BOLD line shows that it has two primary key. would that be possible to create two primary key for a single table??  If not then what does it for?? may be unique key??? anyone can explain me please.     Thanks,      
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend