Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included

Question

I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data. I know nothing really about InnoDB but have been doing regular MySQL database stuff for a while now.

In the below examples there should be a table named corporate_subscription_flag_list with

• A Primary Key: subscription_id
• A Foreign Key: sflid

mysqldump shows a table named `corporate_subscription_flag_list, like:

DROP TABLE IF EXISTS 'corporate_subscription_flag_list';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'corporate_subscription_flag_list' (
    'sflid' int(10) unsigned NOT NULL AUTO_INCREMENT
    'subscription_id' int(11) NOT NULL
    'subscription_flag_id' int(11) NOT NULL
    PRIMARY KEY ('subscription_id')
    KEY 'sflid' ('sflid')
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

However sflid only exists in the above table - am I wrong, or is the way it's been done wrong? sflid isn't a primary key in any other table nor is it a column in any other table in the database.

Looking at the other tables that supposedly use subscription_id as a foreign key to reference corporate_subscription_flag_list it seems, to me, that there is also a problem.

Comments seem to suggest (as does a strange diagram I have found) that corporate_promolist_data (another table) uses subscription_id as a foreign key referencing corporate_subscription_flag_list.

However, unless I'm missing something there is no foreign key being defined, unless KEY 'pludid' ('pludid') is defining a foreign key?

DROP TABLE IF EXISTS 'corporate_promolist_user_data';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'corporate_promolist_user_data' (
    'pludid' int(10) unsigned NOT NULL AUTO_INCREMENT
    'subscription_id' int(11) NOT NULL
    'title' varchar(255) DEFAULT NULL 
    'first_name' varchar(255) DEFAULT NULL
    'last_name' varchar(255) DEFAULT NULL
    'email_address' varchar(255) DEFAULT NULL
    'organisation' varchar(255) DEFAULT NULL
    'job_title' varchar(255) DEFAULT NULL
    'department' varchar(255) DEFAULT NULL
    'address_line_1' varchar(255) DEFAULT NULL
    'address_line_2' varchar(255) DEFAULT NULL
    'town_city' varchar(255) DEFAULT NULL
    'county_state' varchar(255) DEFAULT NULL
    'post_code' varchar(255) DEFAULT NULL
    'country_id' varchar(255) DEFAULT NULL
    PRIMARY KEY ('pludid')
    KEY 'pludid' ('pludid')
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Answer 1

The answer is yes, they are lacking foreign keys, the previous dba was lacking all referential integrity and was using an application stage enforcement of the dba logic (as discussed here: http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html). The previous dba must have been inexperienced and possibly used this method:

For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table

However as me joining the project has proven - it is not necessarily a good way of doing it.

Given the sensitive nature of this project and the later desire to have all kinds of distributed systems accessing the db it has been decided that the overhead of having foreign key constraints is worth it for security when others may be programming and accessing the db.

Whilst my comment on my question shows that it is possible for mysqldump to strip out important stuff and leave one with a misunderstanding, in this case there was no indexing and no foreign key relationships (someone else showed me, I will include how they found the answer if I can catch and get them to tell me).

From what I've been reading and seeing, Drupal does not really commonly use foreign keys, but that's another story altogether.