Solved

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

Posted on 2009-03-31
10
379 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 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now