What to use as an Identification?

Question

0.00/5 (No votes)

See more:

Hy,

I have in SQL a Table on which I use the ID (auto generated) for linking that table to other tables and for identifying Transport Orders for our Drivers (CMR Number). For now I´m linking that table with the Payments and costs over the ID in the Main Order Table. It's no Link in SQl or in VS. I just insert in the linked Table the ID for which it is linked and show the linked tables on a C# win form datagridview.

Last week the old Project/Table (that is running now) with the same structure running on an Access DB had some Problems with the ID-s (every second ID was twice there). By repairing that every Link and CMR Number changed so that every price and cost is lost.

So on SQL every problem and drop of the ID Column would mess up all data.

Is there a better solution to prevent such things in our new C# Application?

As far as I can se there are 3 solutions that i know:
1- Make a running ID that i generate on my own (but i could also make double ID-s and have such problems )
Are there some rules how to make a function like that?
I don´t want to invent the wheel again.

2- use GUID for the linking. Is that a good idea because the SQL filtering linked data in the datagridview. Would it be to slow?

3- Leave it with the ID and hope for the best.

Sorry if it is an stupid question...I´m new to C# and SQL Servers..actually to the whole programming scene :)

Posted 8-Sep-13 9:53am

TarikHuber

Updated 8-Sep-13 18:01pm

Thanks7872

v2

Add a Solution

Comments

TarikHuber 9-Sep-13 3:12am

Thank you for your Soulution.

I use PK in my main Table like ID, Kunde, RefNR..... and an FK in the Paiments Table like ID (the PK), CTID (the FK) .....

Everitnihg is working fine. i dont´have a problem kreating this and using it. I dont't even delete rows in any of my tables. I have just a bit that defines if the row is deleted or not (for the user) and filter the deleted rows.

The Problem is. The PK in the main table (ID) is autogenerated. You never know what could hapen. What if sometime a row is deletede and I copy the Table for some reason. The ID-s would have other values because they woudl be reordered. In that case the FK the FK in the Payments Table wont match to teh rigth Orders.

So the question is not how to make two tables linked (if I am making or understanting something wron,) please let me know). It is how to make the connection/link between the tables so that the link would stay if i move/copy or change the strukture of the main or subtable.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2013-09-08T11:41:00

"It's no Link in SQl or in VS."

Do you mean that you did not put a reference in place between your tables?

That could be the problem. If you want to enforce your referential integrity, you have to set up some reference constraints between your tables, and configure the cascade mode.

Good starting points would be:
Implementing Referential Integrity and Cascading Actions[^]
FOREIGN KEY Constraints[^]

See also SQL SERVER Foreign Keys and Indexes[^]