.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 & Primary Keys

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

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!

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?

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.

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

Listing Foreign Keys

If I run the following code (quote many times on the internet), which should show all foreign keys in a database, but it only returns a partial list:   select CCU.table_name src_table, CCU.constraint_name src_constraint, CCU.column_name src_col, KCU.table_name target_table, KCU.column_name target_col, RC.UPDATE_RULE, RC.DELETE_RULE from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU where CCU.constraint_name = RC.constraint_name and RC.UNIQUE_CONSTRAINT_NAME = KCU.CONSTRAINT_NAME order by CCU.table_name, CCU.constraint_name   If I run the following, removing the KEY_COLUMN_USAGE link, it returns the full list:   select CCU.table_name src_table, CCU.constraint_name src_constraint, CCU.column_name src_col, RC.UNIQUE_CONSTRAINT_NAME, RC.CONSTRAINT_NAME, RC.UPDATE_RULE, RC.DELETE_RULE from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC where CCU.constraint_name = RC.constraint_name order by CCU.table_name, CCU.constraint_name   I can't figure out what the problem is, the ones that are being left out of the first query ad the ones where the unique_constraint is set to the column name, not the constraint's name. For example, the first one appears in both query results, the second one only appears in the secod queries results:   src_table src_constr

Joins on non-indexed fields and table primary keys.


I have two tables (a,b) each with a primary key (which are clustered indexes). When I issue the t-sql statement

delete XX from t1 XX inner join t2 on (xx.A2 = t2

Scripting Foreign Keys in SMO


I am scripting out our database using SMO.

Our database is contained in a schema. When I script out the foreign keys, the resulting script is missing the schema-qualifier on the REFERENCES clause.

My code looks like:


. FileName = strFilePath ;
. AppendToFile = true ;
. DriForeignKeys = true ;
. SchemaQualify = true ;
. SchemaQualifyForeignKeysReferences = true ;

multiple foreign keys to same table and same column


I am working with two Tables. Table A contains Id, Names. Table B  keeps only the reference to table A by InventoryID1, InventoryID12, InventoryID3.

Table A

Table B

I need a select statement to pull up all items from table B, showing the InventoryID 1, 2, 3 by their corresponding Name on Table A  

At first I thought this would be a simple inner join statement, which it does work when only InventoryID1 is involved
select * from Inventory
inner join Menu On (Inventory.InventoryId1 = Menu.Inventory)

The problem arises when I try to obtain the Inventory Name from Table A for the second or third (InventoryID2 or InventoryID3) from Table B. So basically, my problem is how to request a second or third Inventory.Name in the same select statement

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


Processing orphan dimension records with NULL parent foreign keys

<head> <style type="text/css"> .style1 { font-family: Arial, Helvetica, sans-serif; } .style2 { font-family: Arial, Helvetica, sans-serif; font-size: x-small; margin-bottom: 0px; } .style3 { font-size: x-small; } .style4 { font-family: Arial, Helvetica, sans-serif; font-size: x-small; margin-bottom: 0; } .style5 { color: #0000FF; } .style6 { font-family: "Courier New", Courier, monospace; } .style7 { color: #0066CC; font-weight: bold; font-style: italic; } </style> </head>

This link Handling Data Integrity Issues in Analysis Services 2005 is a classic paper on handling data integrity. The snowflaked data structure decribed in it is:

Product Class (dimension)  <--  Product (dimension)  <--  Sales (fact) where some Products do not have a Product Class

My situation is similar, but a bit more complex.   I don't actually have referential integrity problems . . . orphans are valid.

I have two fact tables TableA and TableB. They are related in a [0-or-1] to a [0-or-many] structure.

  • That is, each TableA record may have zero, one, or many TableB records associated with

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

Problem in displaying values for foreign keys in Dynamic data asp.net web app


I have the following tables in my database which are as given under:


Table 2: CREATE TABLE [dbo].[PromotionCodes]( [PromotionCode] nchar NOT NULL, [CustomerPromotionDiscountID] [int] NOT NULL, [EmployeePromotionDiscountID] [int] NOT NULL, [IsActive] [bit] NOT NULL, CONSTRAINT [PK_PromotionCodes_1] PRIMARY KEY CLUSTERED ( [PromotionCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

There is a foreign key relationship between columns (1) PromotionID of table PromotionDiscountValues and CustomerPromotionDiscountID of table PromotionCodes and (2) PromotionID of table PromotionDiscountValues and EmployeePromotionDiscountID of table PromotionCodes

When i browse the PromotionCodes table in the dynamic data listing, the following columns get displayed:

  1. PromotionCode

  2. IsActive

  3. Prom

Denormalising dimension tables with multiple foreign keys


In SSAS 2008 I am trying to "denormalise" 2 source dimension tables into a single dimension.   The main table has 2 FKs to the other  parent table which I am trying to merge in.   What I have done so far is to make 2 copies of the parent table attributes in the dimension and name them accordingly.   Then I set the attribute relationships to link each FK column to the appropriate set of parent columns.   i.e. ending up with something like this:

DIM KEY -> DIM FK1 -> FK1 parent columns

             -> DIM FK2 -> FK2 parent columns

I thought that would be enough for SSAS to realise that FK1 parent columns come via the table relationship involving FK1 and FK2 parent columns come via the table relationship involving FK2.

I was wrong!   When I look at the SQL generated when processing the dimension it seems to choose one of the FKs and uses the same FK when joining both times to the parent table.   i.e. something like:

SELECT DIM.FK1, parent.parent columns from DIM, parent where DIM.FK2 = parent.KEY

Is it possible to do what I am trying??

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

Primary Keys


I just tried creating my first dynamic data site. I created my LINQ to SQL classes and turned on enableTemplates in the web.config.

ran the site, no problem. everything looked good.

tried to add an record to a table, which worked, but adding a second one failed with:

Violation of PRIMARY KEY constraint 'PK_Table'. Cannot insert duplicate key in object 'dbo.Table'. The statement has been terminated.

I poked around the LINQ to SQL designer and found "Auto Generated Value", set that to True but then it failed with

Cannot insert the value NULL into column 'Id', table 'Database.dbo.Table'; column does not allow nulls. INSERT fails. The statement has been terminated.  

I guess that option inferred the database would do the auto generation?


What I want to do set Id to MAX(Id) + 1

In another case, where i have a compound key, I would want to set the Id to MAX(Id) + 1 WHERE Type = @Type (where Type is the second part of the Primary key)

Apologies for the pseudo sql, but I hope you get my meaning.


Before LINQ to SQL i was using transactions and

Visual Studio Shortcut Keys

The following tables describe the shortcut keys in the Microsoft Dynamics AX Debugger.
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