.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

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

Posted By: Rahul     Posted Date: October 13, 2009    Points: 2   Category :Sql Server
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.

View Complete Post

More Related Resource Links

ADO.NET: Achieve Flexible Data Modeling with the Entity Framework


Here the author answers questions regarding the Entity Framework and provides an understanding of how and why it was developed.

Elisa Flasko

MSDN Magazine July 2008

A simple Data Modeling Question


I have a data modeling question that hopefully this fictious example will help explain.  My application is data driven and the classes reflect the tables to a large degree.   In this example my database has two tables:

  • VendorMaster with an int Primary Key of VendorID
  • VendorContact with an int Primary Key of ContactID

The VendorContact table also has a Foreign Key of VendorID in order to associate the contact with a specific Vendor.  In this example a Vendor can only have one contact.

My question is how to represent these two tables in terms of classes.  In my current design I have two classes:

  • VendorMaster
  • VendorContact

In order to represent the relationship between a VendorMaster and VendorContact, my VendorMaster class contains a property called VendorContact.

My questions are about the VendorContact class and how it represents the relationship between a VendorMaster and itself.

Option #1

The VendorContact class should contain an int property of VendorID

Option #2

The VendorContact class should contain a VendorMaster Property

I am not sure which option is the 'best' option to take. My concern is that if I go with option #2 that I would create a cyclical relationship between the VendorMaster and Vendo

Data modeling and dimensional modeling

Hi Team, Can anyone tel me best book or source to learn data modeling and dimensional modeling? Thanks in advance! Regards, Eshwar    

What is the best method for replacing natural keys with surrogates in SSIS?

The title says it all. I have dimension tables with the surrogate keys and staging tables. Currently when I'm creating the fact tables out of the staging tables I'm using subqueries in the OLE DB source select command to get the surrogates. However, this isn't possible always and updating the fact table afterwards doesn't work either without having a temp table. So, is there a way to do this with SSIS transformations?

daily complete cube rebuild four dimensions and fact table including remapping of all surrogate keys

Hi SSIS Engineers: Forgive me if this is a multi-forum question. Our primary activity in the next week is to automate the processing in SSIS, where I led the team to create complete processing flows for Full and Add in the order of Dimension, Measure Group, Partition, Cube, Database. These work. The problem occurs in a complete refresh of the ERP database that caused me manual effort inside SSAS, which I plan to find a way to automate in SSIS. I performed a complete refresh of our cube from the ERP source from a time perspective. We are automating this process in SSIS. In SSAS, I had to manually delete the four dimensions from the UDM view via the Solution Explorer. Since the complete refresh increased the surrogate keys in the dimensions and since the names were the same, I couldn't just drop the partition and reprocess the dimensions, since, in effect, new fact rows would have to be mapped to the new keys. SSAS held on to the old keys even with Full Processing of the Dimensions first, then the Cube. Until I dropped--deleted-- the dimensional tables from the Solution Explorer and the UDM then later readded the dimensions with the new surrogate keys (both add, update and delete dimensional attribute changes in full refresh) via the Add Dimension wizard, the cube kept the old surrogate keys and failed in measure group, fact, database and partition processing.

adding Primary key to a data table

Hi All, I have a data table this table saves values from a form's add button and binds to a gridview on the final submit button adds to the data base. See my Data table below    DataTable dt = new DataTable();                   if (ViewState["CompDetails"] == null)            {                 dt.Columns.Add("CourseCode");                dt.Columns.Add("CourseName");                dt.Columns.Add("Fees");            }            else                dt = (DataTable)ViewState["CompDetails"];            DataRow dr = dt.NewRow();                                &n

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

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

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?

Filtering Hierarchy using Natural Key rather than Surrogate Key using MDX


I'm desingning a report in SSRS based on a SSAS cube, so this is really a MDX/SSAS query rather than a SRSS one

I want to display a list of certain employees some of which are within a hierarchy ie they have a value in the ReportsTo field which holds the surrogate key of their line manager. 

The problem is when i filter the report for a certain subset of people the MDX query is using the unique value which is the surrogate key of the employee which obviously can and will change over time. (eg [Employee].[Reports To].&[2846].CHILDREN)

What i'd prefer is for it to filter based on the natural key of the employee as obviously that stays the same, but obvioulsy the heirarchy is built up off the Surrogate Key rather than the Natural Key.

Is anyone able to give me a few pointers as to whether its a change to the MDX that is required or infact a redeign of the dimension?


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

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

Get primary key values from insert page and route to details page in a Dynamic Data Domain Service A


I finally figured out how to retrieve primary key values from the insert page template that are inserted via defaults in a SQL Server database. I wanted to get the keys so that I could route the user from the insert page template to the details page template (regardless of whether it’s a custom route or not). There are a number of scenarios where separate insert and details pages are desirable, and routing from the insert page back to the list page is not desirable as Dynamic Data does out of the box. Since I could not find an answer to this problem anywhere with significant effort, I decided I would document it for others, I hope you find it useful - it turns out that it's quite simple under 10 lines of code. I also applied the same idea to a Dynamic Data Entities application which works exactly the same except for two minor changes, I have documented it below as well.

For starters, as is documented plenty elsewhere but bears repeating here, you must first annotate your Entity Data Model with ScaffoldColumn = false for identity columns and guids that get assigned with newid() in thedatabase. This tells Dynamic Data not to attempt to pass a null value from the page through to

Data Binding & Autoincrement primary Key Problem


I'm developing a small database using <acronym style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 1px; border-left-width: 0px; border-style: initial; border-color: initial; font-variant: normal; border-bottom-style: dotted; border-bottom-color: #000000; cursor: help;" title="Visual Basic">vb</acronym>.net and MS SQL Server in MS Visual Studio. I developed a data source from the database and dragged a table onto a form in the project using the Data Binding Technique (so all code was automatically generated).
My problem is that I'm unable to auto-increment my primary key on my form. 
When I click 'add new' on the navigator toolbar on the top, the ID textbox (which holds the primary key), stays empty, as no number enters the textbox, not even -1 as I have seen on other example databases. Therefore I'm not able to save information.
When I add data manually to the database, it increments fine as I have set these properties in the database:
  • Identity Spec = yes
  • Is Identity = yes
  • Identity Increment = 1
  • identity seed = 1
Am I leaving something out? Any help would be appreciated.
Thanks i

Binding WPF Controls to an Entity Data Model

In this interview, programming writer, McLean Schofield, demonstrates how to bind WPF controls to an entity data model, using Visual Studio 2010 Beta 1. You can also learn more in the topic: Walkthrough: Binding WPF Controls to an Entity Data Model.

Editing the Data in a GridView

In addition to deleting a GridView's underlying data, another common need is to allow end users to edit the data displayed in a GridView. With ASP.NET 1.x's DataGrid control, editing the data is certainly possible, but requires creating three event handlers and writing a dozen or so lines of code. With the GridView and ASP.NET 2.0, it is possible to create an editable GridView without writing a single line of code! All of the necessary functionality is encapsulated within the GridView.

The GridView allows editing on a row-by-row basis. An editable GridView contains an additional column with an Edit button in each row. When the end user clicks on an Edit button that row becomes editable, causing the Edit button to change to Update and Cancel buttons and the other columns to become TextBoxes. The end user can then update one or more column values and click Update to save their changes.
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