Mysql Question posted in
The official MySQL documentation can be found here.

How can I let each id correspond to a unique set of rows in MySQL?

I have the following database that I want to use for logging networks:

CREATE TABLE ip (
    id INT NOT NULL AUTO_INCREMENT,
    address VARBINARY(16) NOT NULL,  -- IPv4 or IPv6
    PRIMARY KEY (id),
    UNIQUE (address)
);

CREATE TABLE mac (
    id INT NOT NULL AUTO_INCREMENT,
    address VARBINARY(100) NOT NULL,  -- hardware address (usually MAC)
    PRIMARY KEY (id),
    UNIQUE (address)
);

CREATE TABLE network (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE network_configuration (
    network INT NOT NULL,
    mac INT NOT NULL,
    ip INT NOT NULL,
    PRIMARY KEY (network, mac, ip),
    INDEX (mac, ip, network),  -- For searching a network
    FOREIGN KEY (network) REFERENCES network(id),
    FOREIGN KEY (mac) REFERENCES mac(id),
    FOREIGN KEY (ip) REFERENCES ip(id)
);

When logging a network, I do not want any rows to be inserted if the network (combinations of MAC and IP addresses) already exists. In that case I just want to get the corresponding id from the network table.

To be more clear, I want the following query to give a different output for each value of <id>.

SELECT mac, ip FROM network_configuration WHERE network=<id>

Example data:

INSERT INTO ip (id, address) VALUES (1, X'7f000001');  -- 127.0.0.1
INSERT INTO ip (id, address) VALUES (2, X'c0a80101');  -- 192.168.1.1
INSERT INTO ip (id, address) VALUES (3, X'c0a80102');  -- 192.168.1.2

INSERT INTO mac (id, address) VALUES (1, X'3288a01e0fad');
INSERT INTO mac (id, address) VALUES (2, X'e2e1700bd5fb');
INSERT INTO mac (id, address) VALUES (3, X'8b1095dfe2eb');
INSERT INTO mac (id, address) VALUES (4, X'e4d9432afddb');

INSERT INTO network (id) VALUES (1);
INSERT INTO network (id) VALUES (2);
INSERT INTO network (id) VALUES (3);

INSERT INTO network_configuration (network, mac, ip) VALUES (1, 1, 1);
INSERT INTO network_configuration (network, mac, ip) VALUES (1, 2, 2);
INSERT INTO network_configuration (network, mac, ip) VALUES (2, 3, 1);
INSERT INTO network_configuration (network, mac, ip) VALUES (2, 4, 3);
INSERT INTO network_configuration (network, mac, ip) VALUES (3, 3, 1);

(notice how network 2 and 3 share a MAC/IP pair, but are still different networks because network 2 also has an other MAC/IP pair)

Now let’s say I encounter an instance of network 2, that is, mac/ip combinations 8b1095dfe2eb/127.0.0.1 and e4d9432afddb/192.168.1.2.

What is the best way to get this number 2 and not fill my database by making an unnecessary fourth network?

I think I have a make a select query where I input all MAC and IP addresses, but this involves multiple rows and I don’t really see how to do that.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Based on the comment by ysth, I came up with this query for the example case:

    SELECT network FROM network_configuration WHERE network IN (
    SELECT network FROM network_configuration WHERE (mac, ip) IN ((3, 1), (4, 3))
)
GROUP BY network HAVING COUNT(*) = 2

    I wonder if it can be written any nicer though.


  2. 0

    Perhaps your example is incorrect? "127.0.0.1" and "192.168.1.2" are special IPs; they presumably will never be seen from outside the LAN.

    I suggest that the schema is ‘over-normalized’. This should suffice?

    CREATE TABLE network
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE networks (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    network INT NOT NULL,
    mac BINARY(6) NOT NULL,    -- maybe NULL if you don't have mac?
    ip VARBINARY(16) NULL,     -- null when LAN
    PRIMARY KEY(id)
    UNIQUE (network, mac, IP),
    UNIQUE (mac),
    UNIQUE (IP)
);

    Even if this is not sufficient for your application, play around with it to see if this is a better starting point.

    These are local to LANs and do not make sense from outside. (Please correct me if I am wrong):

    127.0.0.*   -- or is it 127.*.*.*
192.168.*.*
10.*.*.*
11.*.*.*

    Note that UNIQUE (but not PRIMARY KEY) allows multiple rows with NULL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search