By rsilbert on
Hello,
I'm new to drupal and web development in general, but an experience programmer in C# and C++. I'm creating a new module that define a new node type and I want to add a couple of tables to the node for specific information beyond the basic node fields. I'm using the Schema API to create my table definitions and I need some clarity as what and how drupal uses Primary Keys, Unique Keys, and Indexes. I've done quite a bit of digging around on the net and in literature but have not found any information explaining these items. Any feedback is much appreciated.
Comments
Why not use the Content
Why not use the Content Construction Kit (CCK)?
I'd like to keep fine grain
I'd like to keep fine grain control and customization over my new node type. From what I've seen, CCK doesn't give you full control. Also, I want to be able install my new module without any dependencies on CCK.
Can you offer any information on Primary Key vs. Unique Key vs. Indexes. Any explanation or insight would be greatly appreciated.
Thank you.
Same issue
I've been dealing with the same thing. I don't have any real experience with MySQL and those terms are pretty vague.
Primary Key: The main key
Primary Key: The main key that determines which record it is..generally this is the auto-inc field and always changing..it's the number used to identify which row you are using.
Unique ID: An auto-inc field that changes numbers everytime a record is entered to guarantee they are always different.
Index: An index is a marker inside the Mysql system which makes it faster to search via that field. If you had a field called "name" and it had the names there, and you indexed it then when you search for it those names are in memory and therefore will show faster than a non-indexed field when searched upon.
The description above was
The description above was very good. I just thought I would clarify one point from it - primary keys must be unique. That means every row has to have a unique value, which is why they are generally auto incrementing fields.
Contact me to contract me for D7 -> D10/11 migrations.
Further key points.
Unique keys may contain nulls (but must remain unique). Primary keys cannot contain nulls. There can only be one primary key. Both primary and unique keys cannot contain duplicates.
Thank you very, very much.
First of all, I'd like to say I only wish I could properly express my gratitude for the above explanations. I may be a CS student, but I never would've gleaned any of that from the technical garble I've found everywhere else.
Secondly, should primary keys be declared as unique keys as well?
No, they already have the
No, they already have the requirement of having to be unique. I'm guessing (though I could be wrong - never actually tried it), that it would probably even throw up an error if you tried to do that
Contact me to contract me for D7 -> D10/11 migrations.
That makes sense.
That makes sense.
I'm assuming primary keys aren't indexed automatically, but that they are index-able, from what I've read in the docs. Any confirmation of that?
And on top of that, is indexing an auto-inc field worth it?
All keys are indexed. An
All keys are indexed.
An auto-incremented field is a prime candidate for the primary key.
Thanks again.
That clears up just about everything I can think of right now. It might not be a bad idea to summarize everything here into an article for the Support pages.
...and then there are foreign
...and then there are foreign keys.
foreign keys
Are those used in Drupal? I've seen the term before, but I haven't the slightest idea what they are or what they're used for.
I've tried to use foreign key
I've tried to use foreign key support in Drupal before, but couldn't get it to work. If someone knows how to do it, I'd be interested.
A foreign key is when the value in that column MUST exist in another table.
Contact me to contract me for D7 -> D10/11 migrations.
A foreign key is a reference
A foreign key is a reference from a row or rows in one table to a single row in a table (which may be the same table) identified by a primary key or a key which would qualify as a primary key (candidate key).
As an example, suppose you have a CUSTOMER table with CUSTNO as the primary key and an ORDER table with ORDERNO as the primary key and a CUSTOMER_CUSTNO column that identifies the corresponding row in the CUSTOMER table. If CUSTOMER_CUSTNO has a foreign key constraint applied then the DBMS will ensure referential integrity between the two tables.
With a foreign key constraint applied, if a CUSTOMER is deleted or updated, any corresponding ORDER rows will be taken into account and by default a CUSTOMER cannot be deleted (or CUSTNO changed) if any correspong ORDER rows exist. Similarly, an ORDER cannot be created with a none null CUSTNO without a matching CUSTOMER row.
You can define the foreign key constraint with a cascade action so that deleting a CUSTOMER will also delete all corresponding ORDER rows.
In the above example you could define the foreign key as:-
Instead of CASCADE, the action could be defined as RESTRICT (the default), SET NULL or NO ACTION.
CASCADE will delete corresponding ORDER rows when a CUSTOMER is deleted
SET NULL will set the CUSTOMER_CUSTNO to null in corresponding ORDER rows when a CUSTOMER is deleted
REJECT and NO ACTION will prevent the deletion of a CUSTOMER or corresponding ORDER rows exist.
In addition to the ON DELETE action, an ON UPDATE action can be defined which defaults to REJECT.
Foreign key referential inegrity is only supported by InnoDb and comes at a performance cost and may require tables to be loaded in a particular sequence.
Note that it is probably a bad idea to delete orders when deleting a customer and in practice you would set the ON DELETE action to SET NULL rather than CASCADE.
There is a discussion on the use of foreign keys at http://stackoverflow.com/questions/18717/are-foreign-keys-really-necessa...
Reliability
So, really, foreign keys just ensure data reliability between tables then, right? I know various Drupal tables use "uid", but none of them are foreign keyed, from what I can see. Though, that may just have to do with the fact that I'm using MySQL rather than InnoDB.
Also, is there a standard for using or not using foreign keys in Drupal? I'd imagine that it's preferred that they be avoided given the performance costs.
Foriegn key constraints
Foriegn key constraints enforce data integrity, foreign keys (ie indexes) are for relationships between tables.
InnoDb is a MySQL engine (as is MyISAM).
As far as I am aware foreign key constraints are not used in Drupal.
PK and FK Implementation
I have a RDB which I am implementing in Drupal 6. Any idea how I can do this in CCK? The starting point could be using node reference but I can seem to use it to my benefit. Not very old to Drupal, only the sixth month, so please provide hints or documentation.