Can you have multiple Keys in SQL and why would you want that?_问答_开发者

Is there a reason why you would want to have multiple KEYs in a TABLE? What is the point of having mult开发者_如何学JAVAiple KEYs in one table?

Here is an example that I found:

CREATE TABLE orders(
id INT UNSIGNED NOT NULL AUTO INCREMENT,
user_id INT UNSIGNED NOT NULL,
transaction_id VARCHAR(19) NOT NULL,
payment_status VARCHAR(15) NOT NULL,
payment_amount DECIMAL(15) NOT NULL,
payment_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(id),
KEY(user_id),
)

Also, you'll notice the DBase programmer doesn't make transaction_id a KEY. Is there a reason for this?

KEY in MySQL is an alternate syntax for "index".
Indexes are common across databases, but they aren't covered by ANSI as of yet -- it's pure miracle things are as similar as they are. It can be common to have more than one index associated to a table -- because indexes improve data retrieval at the cost of update/delete/insert speed.

Be aware that MySQL (5.x?) automatically creates an index if one doesn't already exist for the primary key of a table.

There are several possible reasons.

Enhance search performance (when the WHERE clause uses KEY fields, it performs faster)
Restrain table contents (when using a UNIQUE key for a column, it can't have the same value twice or more on it)

These are the most common reasons.

In SQL you may only have one PRIMARY KEY per table.

KEY(foo) is bogus syntax in standard SQL. In MySQL KEY foo is a poorly named synonym for INDEX foo and does not impose a UNIQUE constraint. (It is poorly named because it does not actually relate to the functioning of a key.)

There may be multiple UNIQUE INDICES which can play the role of "candidate keys". (A unique constraint can be specified without an associated INDEX, but the point of a "key" is generally a quick look-up.) The point of a PRIMARY KEY is to uniquely identify a single record and is almost exclusively INDEX-backed and may even be directly related to the clustering of the data.

Only the minimal amount of [unique] INDICES required to ensure data validity and meet performance requirements should be used -- they impose performance penalties on the query engine as well as have additional update and maintenance costs.

Non-unique INDEX's (INDEX foo, or in the case of MySQL, KEY foo) are purely to allow the database to optimize queries. They do not really map to "keys" and may be referred to as "covering indices"; if selected by the query planner these indices can aid in performance even though they add nothing to the logical model itself. (For performance reasons, a database engine may require that FOREIGN KEYS are covered by indices.)

In this case, creating an INDEX (don't think "KEY"!) on user_id will generally (greatly) speed up queries with clauses like:

... WHERE user_id = {somenumber}

Without the INDEX the above query would require a FULL TABLE SCAN (e.g. read through all records).

While I do not know why transaction_id is not made an index, it might not be required (or even detrimental for the given access patterns) -- consider the case where every query that needs to be fast either:

Does not use transaction_id or;
Also has a user_id = ... or other restriction that can utilize an INDEX. That is, in a case like WHERE user_id = ... AND transaction_id = ... the query planner will likely first find the records for the matched user and then look for the matching transaction_id -- it still has to do a SCAN, but only over a much smaller data-set than the original table. Only a plain WHERE transaction_id = ... would necessarily require a FULL TABLE SCAN.

If in doubt, use EXPLAIN -- or other query analyzer -- and see what MySQL thinks it should do. As a last note, sometimes estimated query execution plans may differ from actual execution plans and outdated statistics can make the query planner choose a non-ideal plan.

Happy coding.

"Keys" might refer to one of:

Index (search optimization)
Constraint (e.g. foreign key, primary key)

You may want multiple because you need to implement more than one of these features in a single table. It's actually quite common.

In database theory, a key is a constraint that enforces uniqueness. A relvar (analogous to a SQL table) may indeed have more than one candidate key. A classic example is the set of chemical elements, for which name, symbol, atomic number and atomic weight are all keys (and folk will still want to add their own surrogate key to it ;)

MySQL continues a long tradition of abuse of the word KEY by making it a synonym for INDEX. Clearly, a MySQL table may have as many indexes as deemed necessary for performance for a given set of circumstances.

From your SQL DDL, it seems clear the ID is a surrogate key, so we must look for a natural key with not much to go on. While transaction_id may be a candidate, it is not inconceivable that an order can involve more than one transaction. In practical terms, I think an auditor would be suspicious of multiple orders made by the same user simultaneously, therefore suggest the compound of user_id and payment_time should be a key. However, if transaction_id is not a key then the table would not be fully normalized. Therefore, I'd give the designer the benefit of the doubt and assume transaction_id is also a key.