[Feature Request]: Database schema consistency: Primary Key/Foreign Key type mismatch

[iabok]

Preflight Checklist

• I have read the Contributing Guidelines for this project, if it exists.
• I agree to follow the Code of Conduct that this project adheres to.
• I have searched the issue tracker for a feature request that matches the one I want to file, without success.

Problem Description

@dmohns This is Isaac from Ignite Energy Access: Consider the following DDL.

CREATE TABLE `meter_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `online` tinyint(1) NOT NULL DEFAULT 0,
  `phase` int(11) NOT NULL DEFAULT 1,
  `max_current` int(11) NOT NULL DEFAULT 10,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `meters` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `serial_number` varchar(191) NOT NULL,
  `meter_type_id` int(11) NOT NULL,
  `in_use` tinyint(1) NOT NULL DEFAULT 0,
  `manufacturer_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `connection_type_id` int(11) NOT NULL,
  `connection_group_id` int(11) NOT NULL,
  `tariff_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `meters_serial_number_unique` (`serial_number`)
)

CREATE TABLE `access_rate_payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `meter_id` int(11) NOT NULL,
  `access_rate_id` int(11) NOT NULL,
  `due_date` datetime NOT NULL,
  `debt` double NOT NULL DEFAULT 0,
  `unpaid_in_row` double NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `meter_tariffs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `price` double NOT NULL,
  `currency` varchar(20) NOT NULL,
  `factor` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `total_price` double DEFAULT NULL,
  `minimum_purchase_amount` double NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
)

Most primary keys are INT(10) UNSIGNED, while foreign-key-like columns (e.g., meter_type_id, manufacturer_id, tariff_id, meter_id, access_rate_id) to name but a few are INT(11) signed. See the schema above. These should match exactly (size and unsignedness).

If you add foreign keys later, MySQL will require matching definitions; So, as-is, you can even store negative IDs in foreign keys, which is logically invalid.

Proposed Solution

N/A

Alternatives Considered

N/A

Additional Information

N/A

[dmohns]

Hey @iabok

that's a great find. I think this ultimately boils down to the use of

$table->increments('id');

which by default creates an unsigned integer. But

$table->integer('mpm_meter_type_id');

creates a signed integer.

Should be an easy task to simply convert FK to unsigned int, as our ID's should never be negative anyways. However, the more interesting question will be, how we can detect all these cases 🤔

[iabok]

@dmohns Thank you for your feedback. You have a good point! Just ran this query on database:

SELECT
  c.table_name,
  c.column_name,
  c.column_type,
  c.is_nullable,
  c.column_default
FROM information_schema.columns c
WHERE c.table_schema = DATABASE()
  AND c.column_name LIKE '%\_id'
  AND c.data_type IN ('int','bigint')
  AND c.column_type NOT LIKE '%unsigned%'
ORDER BY c.table_name, c.column_name;

This is the closest i could get:
Sample Image

Full list
tables_with_foreign_keys.pdf

Also something to note:
Across the schema, owner_id operates as both a polymorphic identifier (alongside owner_type) and a referential key in specific tables (This is in reference with application relationship level association) . In such a instance, we might have to omit foreign key constraints.