2

My (shortened) table definition is:

CREATE TABLE bus 
(
    id integer NOT NULL PRIMARY KEY,
    name character varying NOT NULL CHECK (name<>'')
);

CREATE TABLE network_node  
(
    id integer NOT NULL,
    name character varying NOT NULL CHECK (name<>''),
    ecu integer NOT NULL REFERENCES ecu,
    bus integer NOT NULL REFERENCES bus,
    UNIQUE(ecu,name),
    UNIQUE(bus,name),
);

CREATE TABLE controller_application 
(
    id integer NOT NULL,
    name character varying NOT NULL CHECK (name<>''),
    network_node integer NOT NULL REFERENCES network_node,
    address smallint NOT NULL CHECK (name BETWEEN 0 AND 253),
    bus integer NOT NULL REFERENCES bus,
    UNIQUE(network_node,name),
    UNIQUE(bus,address),
);

The relationship I am trying to represent in the database is:

  • A bus contains zero or multiple network_nodes
  • A network_node owns zero or multiple controller_applications
  • Indirectly controller_applications relate to exactly one bus.
  • An ecu contains zero or multiple network_nodes

The constraints I am trying to represent in the database are:

  • network_node names have to be unique per ecu
  • network_node names have to be unique per bus
  • The addresses of the controller_applications have to be unique on each bus

You will also see that there is currently redundant hierarchical information in the structure, as controller_applications and network_nodes both contain the bus field.

  • 1
    You need to change the (network_node) FK ro include the bus. Something like: FOREIGN KEY (bus, network_node) REFERENCES network_node (bus, id) and add a unique constraint on network_node (bus,id) as well (for this new FK to work). – ypercubeᵀᴹ Aug 4 '15 at 12:24
  • For the "problematic", you already have UNIQUE(bus,address), where is the problem? – ypercubeᵀᴹ Aug 4 '15 at 12:37
  • @ypercube Writing problematic was a bad idea. I give an example of a sequence which would produce inconsistent data. <!-- language: lang-sql --> INSERT INTO network_node(id,name,ecu,bus) VALUES (1,'B1_NN1',1,1); INSERT INTO controller_application(id,name,network_node,address,bus) VALUES (1,'NN1_CA1',1,1); INSERT INTO controller_application(id,name,network_node,address,bus) VALUES (1,'NN1_CA1',1,2); This obeys the constraints, but the bus given in the last controller_application is not the same as in the network_node. – Torsten Knodt Aug 4 '15 at 13:10
  • correct. The suggestion I made in my first comment will avoid this issue. You won't be able to insert inconsistent (for this constraint) data. – ypercubeᵀᴹ Aug 4 '15 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.