.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

foreign key to reference composite primary key

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

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

Foreign & Primary Keys


I have the following FK on Items table

ALTER TABLE [dbo].[items]  WITH NOCHECK ADD  CONSTRAINT [FK_items_i_tmpnam] FOREIGN KEY([i_tmpnm_id])

REFERENCES [dbo].[i_tmpnam] ([tmpnam_id])



Yet I'm able to delete all records from i_tmpnam and leave orphans in the FK field in the Items table. So, does NOCHECK disables the constraint completely?


Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

primary key and foreign key function generated values



how to generate invoice type of key ex:emp100 with unique identifier

i generated invoice type by creating function by concatenate string and unique identifier 

but it can't be reference to another table

 can i have any other types generating invoice type primary key and used as a reference type


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



SQL Server Mgmt Studio - primary/foreign keys


I currently have a number of tables in my database, which include these 2 tables:

tblOne - Uid(pk), FirstName, LastName, Email

tblTwo - HCCID(pk), Uid, Category, Description, Company, Manager


In my application I have users from tblOne logging in and creating records which are inserted into tblTwo. Thankfully this is working ok, however, my problem is with the Uid in tblTwo (fk).

When a new record is created in tblTwo, the HCCID(pk) iterates, so i have a numeric identifier for each record. However is there any way i can also keep a record of the user by recording the Uid in tblTwo based on which user creates the record?

Seeing as though this is already a pk in tblOne and I also already have a pk in tblTwo, is this possible?

Any ideas will be greatly appreciated!

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

When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys

When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys replicated?

Foreign key to composite key

Well...I have to create a sale detail table consisted of quantity and
 product and has to have reference to the product table
 and the order table as well.


Create table [sales detail]
 OrderNo int,
 ProductId char (5),

 add constraint pk_sales_detail primary key(OrderNo, ProductId), 
 Quantity int not null,
 ProductName varchar(50) not null

alter table [sales detail]
add constraint
 fk_OrderNo_sales_detail foreign key (OrderNo) references
 Orders (OrderNo) ///This works fine

alter table [sales detail]
add constraint fk_ProductId_sale_detail foreign key (ProductId)
references Products (ProductId) ///This gives me errors about no 
   ///indicate primary key or something

Also note that: the ProductId in the Products Table is part of the composite key. Not sure if that is the cause of the problem.

create table orders
 OrderNo Int,
  constraint pk_OrderNo primary key (OrderNo),
 OrderDate Date not null,
 CustomerNo char(4) not null,
 SalesRep char(3) not null

create table products
 ManufacturerId char(3),
  Check(ManufacturerId like '[A-Z][A-Z][A-Z]'),
 ProductId char(5),
  Check(productId like '[A-Z][A-Z][0-9][0-9][0-9]'),
  constraint pk_ManufactureId primary key (ManufacturerID, ProductID),
 Description varchar(50) not null,
 Price money not null,
 QtyOnHand int not null

Here is what I would like to do: Add a “Sales Detail&r

Foreign Key Drop downlist available in edit mode even though part of primary key


I am using Dynamic Data 4 in VS2010 with EF4.

So I have noticed and read that DD using Entity Framework does not allow editing of primary keys in a scaffolded table...fair enough...I don't know if its a difference between developing in SQL Server vs Oracle but often in our database we use composite primary keys that consists of several columns in a table (as in not generated ids, or unique ids as the primary key).  
So I have a table that has a primary key that that consists of two text fields and a foreign key from another table.  I have a custom entity template that i use to insert new records into the table.  When I select new, I get the desired behavior of a dropdown for the foreign key field to pick to insert a new record.  That works great.  When I select edit on a row in the table, I still get that dropdown!  Of course if I select a different entry from the dropdown DD yells at me since the foreign key field is part

So I have noti

need to change primary key to a composite primary key.


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?

A better way to reference your wizard steps using named steps

Note: this article uses the plain vanilla but the concepts apply equally well to its popular counterpart .

By far the most common way that I see wizard steps reference in code snippets is by their index.

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.

reference from menubuttons to text parts in article on the same page


Hi, I'm searching now for hours but couldn't find it.Cry

In my menu i have several buttons which (by a <a href..) should go the a part of an article that is in the same form.

Menu button : What is a database should go to the page default.aspx and in there is an article published.
A part of this article is having a piece of text about "what is a database"

Menu button: how to make a query should go to this same article in the same page but then to the part that is have the piece about the query. The next could be an example of what i'm doing:

    <li><a href="default.aspx ?????>what is a database</a></li>
    <li><a href="default.aspx ?????>what is a query</a></li>

Where my question marks are, there should be a connection to that particular piece of text.
In each article part there is an ID="text1" and the second part id="text2"

How shoudl i do that, Should i use a question mark.
Please give me some guidance how to structure the <li> statement or a pointer where I can read about it.


Reference HttpApplication from a separate HttpApplication


I want to create a website that can monitor the status of other websites running on the same server. Is this possible? How do I get a reference to a site's HttpApplication context from a separate HttpApplication?

Registering controls of current assembly object reference NOT set error


Hi There,

I have created a simple ASP.net Web Application (2.0) and have added a web user control.

Now in my page I have added this control but I have registered like this,

<%@ Register Assembly="WebApplication1" Namespace="WebApplication1" TagPrefix="Custom" %>

Which means I want to access all the controls in my current Web Application project

Then I have added my user control like this,

<Custom:MyUserControl ID="MyUserControl1" runat="server" PublicProperty="Hello" />

Every thing is fine but when I execute the project, NO control on my user control gets "instantiated" and thus I receive error "Object Reference Not Set"

Note that My Control has,

1) Public property PublicProperty as String

2) 1 Label control

In Load Event Handler I am setting Text property of Label Control to PublicProperty. Here object reference NOT set error occurs (which means Label control is NOT initialized).

I don't want to register my control using Src, TagPrefix etc. Any ideas?


Composite Control which contains arbitrary content defined in .aspx of parent and thier ViewState


I want to write own control which can contain other. And I want to define content of the control in the .aspx file where the control is defined. I have written such control. But now I have issue with ViewState of inner controls of my control. The following samle illustrates the issue: I defined two asp:DropDownList ID="ddl1" and ID="ddl2" One of them is inside of my control and other is outside. When posback is occurred asp:DropDownList ID="ddl1 loses state and becomes empty. How to say ASP.net to store state of inner controls defined by this way?


<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="MyControl.ascx.cs" Inherits="DynamicControls.Controls.MyControl" %>

<div class="box">
    <div class="Header-Left">
        <div class="Header-Right">
            My Control
    <div class="Content">
        <asp:PlaceHolder ID="contentPlace" runat="Server" />

video tutorial link for composite and rendered custom server control


hii all

i am new to technology so go easy on this post

according to what i read from web there are 3 type of custom server controls

1 superclass

2 composite

3 rendered

i found video tutorial for superclass custom server control but couldnt find video tutials for other 2.

please forward me link for composite and rendered custom server controls video tutorial  

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