Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-03-31
10
Medium Priority
?
404 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

972 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