I have defined a new content type, statetype and a CCK field, state.
I need to use the CCK field as primary key in this table because I have another table and I need use that field as foreign key. (This code and table is not Drupal and i wrote it myself, but it uses a Drupal table.) I should change state field to primary key in the "statetype" table but Drupal already defines vid as primary key. Is there any problem if I define multiple primary keys (vid, state) or if I remove vid as primary key?
-
This question is not specific to Drupal; primary keys are primary keys independently from Drupal. The answers you get would be valid even if you would be using another CMS; the only parts specific to Drupal are the name of the table, and its schema.– apaderno ♦Apr 3, 2011 at 9:44
-
Remember to accept answers for your questions; if users don't accept answers for their questions, this site doesn't have any chance to go past the beta phase.– apaderno ♦Apr 30, 2011 at 12:19
Add a comment
|
1 Answer
A database table can have at most one primary key, but it can have multiple unique keys.
Replacing the primary key in that table is not a good idea, because Drupal code rely on the fact the primary key is vid. You cannot set any field as primary key: it must a field that contains unique values for each row; if the field you choose can have the same value for multiple rows, then it's not a candidate for primary key.
answered Apr 3, 2011 at 9:40
-
thank u. my state field is unique. can i change priary key from vid to state. is there any problem?– user780Apr 3, 2011 at 10:12
-
no. @kiamlaluno says: "..., because Drupal code rely on the fact the primary key is vid." Apr 3, 2011 at 20:37
-
Sort of correct, and sort of wrong. A composite primary key can consist of several columns. See lists.mysql.com/mysql/832– Fuzzy76Apr 6, 2011 at 11:12
-
@Fuzzy76: What I reported is still valid, even in the case the primary key is made of more fields; a database cannot have two rows with the same value for the primary key, and cannot have two primary keys.– apaderno ♦Apr 6, 2011 at 11:48