Database; One or two unique fields?

I will use a field named ID; an integer primary key. Is it any point to also specify a second unique field, to be able to reconstruct the tables if some ID f开发者_如何学运维ields/relationships are messed up, or is that not a worry?

I'm using auto-increment integer, with Sql-CE database. Single user.

If the semantics of your table require it, then add however many unique keys as you may need. For instance, if in your problem domain all "Employee" entities are uniquely identified by Name, then Name should have a unique index or constraint on it. If there can be two employees with the same name, then, of course, there should be no uniqueness constraint on that column.

I have a client now who needs to keep information per-school, per-fiscal year. They need a unique constraint (or index) on the school and year columns, combined. Sometimes these two are also the primary key, but even when they're not primary, they are unique.

I don't really understand your motivation to add a second unique ID field - what for?? What's the benefit you see from that??

What you need is an ID that is unique, stable, and able to clearly identify each individual row and can serve as your primary key - an INT IDENTITY in SQL Server does that perfectly well.

I don't see any point or gain in adding a second unique ID to each row...

Every table has one or more candidate keys. Pick one to be the primary key; add UNIQUE constraints on the others to enforce semantic correctness. It has nothing to do with being able to reconstruct the table if something is "messed up".

You should also have indexes on columns that appear in WHERE clauses to make access as fast as possible. Those are application/use case specific.

An example of what I mean would be an address table. You might have a surrogate primary key. You'd also have indexes on different combinations of zip, city, province, county, etc. to make SELECTs efficient. You might also want to have the street1/stree2/city/province/zip combination to be UNIQUE. Depends on your business problem.

A table should have as many keys as it needs to endure data integrity. If "ID" in this instance means a surrogate key then the answer is yes, you generally ought to have a natural key to ensure uniqueness in a properly normalized database - otherwise you could be duplicating meaningful business data. If you are identifying surrogate keys before natural keys then I think you are taking a flawed approach to design.

Good criteria for choosing keys are: familiarity, simplicity and stability. Add surrogate keys to your model only when and where you have good reason to, having regard the possible disadvantages and additional complexity.

Redundancy for the sake of backup can be a worthwhile design.

However, most people do not implement it by loading redundant columns into the schema tables, as your question suggests. There is a lot of overhead in doing it that way, and there are a lot of failure modes where the duplicate ID gets screwed up at the same time that the main ID gets screwed up.

You might be better off designing history tables that keep track of the history of IDs over some limited timespan. A lot of people design history tables as a staging area for off line storage, with only recent history retained in the database. This kind of history can address other problems in addition to the one you are interested in.