Solved

creating multi field key in c# csharp sql server express 2005 .net

Posted on 2009-03-31
10
391 Views
Last Modified: 2012-05-06
Just started using MS SQL Server 2005 Express, I got a table with 4 fields. This table has no primary key, but 3 of the columns could "come together" and make a key.

In MS Access, I would simply select the multiple fields in design view, and click the 'Key' button and viola. In C# Visual 2005 SQL Designer, I can select the 3 fields, and then create a key that allows unique only records.

The thing is, if a value repeats in the same field but in different records, then I can't commit the record, even though the other 2 of 3 columns that make the key are different.

Therefore, I'm probably not defining the key based on the 3 fields correctly?!

How do I do it? Please help... thanks!
0
Comment
Question by:yaronusa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24032524
Why not add a primary key as an identity field and make a unique index on the other 3 columns as your natural surrogate key.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24032527
Why not add a primary key as an identity field and make a unique index on the other 3 columns as your natural key.

should not have had surrogate, the identity is your surrogate.
0
 

Author Comment

by:yaronusa
ID: 24039995
I tried what you suggested and it doesn't seem to work, but maybe I'm wrong.

Below you will find three images: image 1 shows my table, image 2 shows two different error notifications.

After creating a new field, marking it primary key, and having it auto-increment, I tried the following settings permutations for columns 2-4:

Type, Duplicates Ignored, Is Unique = Result
------------------------------------------------------------
Index, No, Yes = Error Notification 1
Index, No, No = Error Notification 2
Unique Key, No, Yes = Error Notification 1


The problem with Error Notification 1
--------------------------------------------------
Is that it commits the record to the table, when it should not allow it.

The problem with Error Notfication 2
-------------------------------------------------
Is that I am not allowed to have duplicate data in any independent column 2-4, even if the remaining data in the remaining columns 2-4 is different. In other words, I wouldn't be able to do the following:

existing record: 4, a, b, c, misc
new record not allowed: 5, a, x, y, misc

Above, the new record cannot be added because field 2 value 'a' matches 'a' in another column.

So............................. what should I try to do next? any ideas?

tableOverview.bmp
Error-Notification-1.bmp
Error-Notification-2.bmp
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:yaronusa
ID: 24040059
************ CORRECTION (See BOLD statement below) ****************

Type, Duplicates Ignored, Is Unique = Result
------------------------------------------------------------
Index, No, Yes = Error Notification 1
Index, No, No = Allows Duplicates (See Image below)
Unique Key, No, Yes = Error Notification 1

allowsDuplicates.bmp
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 500 total points
ID: 24040877
Since varchar(max) columns can't have indexes on them, I assume you have some sort of a trigger validating your input.  Can you verify this and post the trigger code if there is.
0
 

Author Comment

by:yaronusa
ID: 24042626
OK, you solved my issue. Coming from MS Access, I didn't know that I couldn't do that with the varchar(max).

I changed those fields to varchar, and now everything works.

Thank you for taking the time to help.
0
 

Author Closing Comment

by:yaronusa
ID: 31565007
OK, you solved my issue. Coming from MS Access, I didn't know that I couldn't do that with the varchar(max).

I changed those fields to varchar, and now everything works.

Thank you for taking the time to help.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24042799
The maximum size of an index is 900 bytes.  So that means that all fields included in the index must add up to no larger than 900 bytes.

INT=4 bytes
BIGINT=8 bytes
varchar(900)=900 bytes
nvarchar(450)=900 bytes

nvarchar(max), varchar(max), varchar(901)+ and nvarchar(451)+ are all non-indexable.  Unless you are going to index for EQUALITY only, at which point there is something that can be done.

I plan on covering the topic of indexed equality searching on large data strings on my site http://sqlservernation.com.  Stop over and join to be sure you see it when I post it later this week.
0
 

Author Comment

by:yaronusa
ID: 24043268
I actually already knew about sqlservernation because I checked your profile when you first responded, and will be signing up shortly!
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24043541
Cool.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question