mysql storing ipv6 address with foreign key constraint

Question

I have read all the questions from References, and understand that best way to storing the IPv4/IPv6 address for indexing is 2xBIGINTs. But, how can I index those in another table using foreign key with best perfomance?

My requirement is simple. I am trying to make a table for storing IP Address (IPv4, IPv6) with mask (table called subnets). Now, I have another table called interfaces where each interface can have an ip address. What is the best way to apply a foreign keys between subnets and interfaces table.

I have thought of this right now...

--
-- Table structure for IP addresses `subnets`. stores the IP addresses with version (v4/v6) and type (unicast/multicast)
--

CREATE TABLE subnets (
    top_ip BIGINT UNSIGNED DEFAULT NULL COMMENT 'top half of the IP Address in INT. If IPv4, then its NULL',
    bottom_ip BIGINT UNSIGNED NOT NULL COMMENT 'bottom half of the IPv6 Address or full IPv4 address in INT',
    mask INTEGER UNSIGNED NOT NULL COMMENT 'IP subnet mask',
    version CHAR(2) NOT NULL DEFAULT '' COMMENT 'IP version. can be v4 or v6',
    type VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'type of address. can be unicast or multicast',
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_ip_addr (top_ip, bottom_ip)
    PRIMARY KEY (top_ip, bottom_ip)
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

--
-- Table structure for table `interfaces`
--

CREATE TABLE device_interface (
    intf_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    intf_top_ip BIGINT UNSIGNED DEFAULT NULL,
    intf_bottom_ip BIGINT UNSIGNED NOT NULL,
    CONSTRAINT subnet FOREIGN KEY (intf_top_ip, intf_bottom_ip) REFERENCES subnets(top_ip, bottom_ip),
    intf_name VARCHAR(100) NOT NULL,
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

References:

I know this doesn't answer your question but if switching database software is an option you should look at PostgreSQL. It has much better data types for this kind of usage. — Sander Steffann, Nov 23 '14 at 22:35

score 0 · Accepted Answer · answered Nov 24 '14 at 06:28

Well, after researching a lot... I have simply decided to just use one column of VARBINARY(16) to store IPv4/IPv6 address and use it as PRIMARY KEY instead of 2 BIGINT columns just because its a bad idea to break a simple IPv6 address into two separate columns for manageability purpose. And I really doubt the performance will have any major -ve impact (it would definitely have if I had used VARCHAR instead)

mysql storing ipv6 address with foreign key constraint

References:

1 Answers1