Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-03-31
10
Medium Priority
?
406 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
  • 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

Technology Partners: 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!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

564 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