Database Design: use a non-key as a FK?

Say I have the following table:

TABLE: widget
 - widget_id (not null, unique, auto-increment)
 - model_name (PK)
 - model_year (PK)

model_name and model_year make up a composite key. Is开发者_开发百科 there any problem to using widget_id as a FK in another table?

A key is any number of columns that can be used to uniquely identify each row within the table.

In the example you've shown, your widget table has two keys:

• model_name, model_year
• widget_id

In standard SQL, a foreign key may reference any declared key on the referenced table (either primary key or unique). I'd need to check MySQLs compliance.

From MySQL reference manual on foreign keys:

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

As an alternative, if you wish to use the composite key from your referencing table, you'd have two columns in that table that correspond to model_name and model_year, and would then declare your foreign key constraint as:

ALTER TABLE OtherTable ADD CONSTRAINT
     FK_OtherTable_Widgets (model_name,model_year)
     references Widgets (model_name,model_Year).

Re InnoDB vs MyISAM, in the docs for ALTER TABLE

The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB storage engine, which implements ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 13.6.4.4, “FOREIGN KEY Constraints”. For other storage engines, the clauses are parsed but ignored. The CHECK clause is parsed but ignored by all storage engines. See Section 12.1.17, “CREATE TABLE Syntax”. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.8.5, “MySQL Differences from Standard SQL”.

I have no experience specific to my-sql, but with database-modeling in general

It is really important to understand the difference between primary and secondary keys. Even if many db (I know for sure Oracle does) permit to specify an unique (simple or composite) key as the FK target, this is not considered a best practice. Use the PK instead.

FK to a secondary key should be used imo only to relate to tables that are not under your control.

In your specific case, I would certainly FK to widget_id: that is because the widget_id should be your PK, and the composite only made unique (and not null of course). This leads to better performance in mane cases, as you join only one column in queries, and is generally considered a best practice (google 'surrogate key' for more info)

MySQL will create an index on the column if there isn't one it can use:

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Let's say you have two tables. The first table looks like this:

TABLE: widget
 - model_name (PK)
 - model_year (PK)
 - widget_id (not null, unique, auto-increment)

If you want to make another table that refers to unique records in the first table, it should look something like this:

TABLE B: sprocket
 - part_number (PK)
 - blah
 - blah
 - model_name_widget (FK to widget)
 - model_year_widget (FK to widget)
 - blah 

With compound primary keys, you have to include all key fields in your FK references to make sure that you are uniquely specifying a record.