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

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!
yaronusaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
BrandonGalderisiCommented:
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
 
yaronusaAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
yaronusaAuthor Commented:
************ 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
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
yaronusaAuthor Commented:
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
 
yaronusaAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
yaronusaAuthor Commented:
I actually already knew about sqlservernation because I checked your profile when you first responded, and will be signing up shortly!
0
 
BrandonGalderisiCommented:
Cool.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.