CODEWITHSUNDEEP
oracle-databaseforeign-keysrdbms
yankee
yankee

Reputation: 125

Composite FK with nullable fields

I have 3 tables:

but one of 1) and 2) is mandatory.

In case of 1) the netOpId must exists in NETWORK_OPERATOR table;

In case of 2) the cellId+netOpId must exist in CELL table;

Here is a sample DDL code:

CREATE TABLE "NETWORK_OPERATOR" (
  "NETOPID" INTEGER NOT NULL, 
  "NAME" VARCHAR2(20),
  CONSTRAINT "NETWORK_OPERATOR_PK" PRIMARY KEY ("NETOPID")
)

CREATE TABLE "NETWORK_CELL" (
  "CELLID" INTEGER  NOT NULL, 
  "NETOPID" INTEGER  NOT NULL, 
  "NAME" VARCHAR2(20), 
  CONSTRAINT "NETWORK_CELL_PK" PRIMARY KEY ("CELLID"),
  CONSTRAINT "CELL_NETOPS_FK" FOREIGN KEY ("NETOPID") REFERENCES "NETWORK_OPERATOR" ("NETOPID")
)

CREATE TABLE "IRI" (
  "IRIID" INTEGER NOT NULL,
  "NETOPID" INTEGER,
  "CELLID" INTEGER,
  "NAME" VARCHAR2(20),
  CONSTRAINT "IRI_PK" PRIMARY KEY ("IRIID"),
  CONSTRAINT "IRI_NETOPS_FK" FOREIGN KEY ("NETOPID") REFERENCES "NETWORK_OPERATOR" ("NETOPID")
)

In other words,

a NETWORK_CELL is itself always bound to a NETWORK_OPERATOR, so that IF a IRI has a netOpId it should be enforced to be an existing netOpId, ELSE IF a IRI has a cellId+netOpId it should be enforced to be an existing cellId+netOpId

I see 2 options:

Option 1:

Make only IRI.NETOPID NOT NULLable and add a composite FK

    CREATE TABLE "IRI" (
      ...
      "NETOPID" INTEGER NOT NULL,
      "CELLID" INTEGER,
      ...
      CONSTRAINT "IRI_CELL_FK" FOREIGN KEY ("CELLID", "NETOPID") REFERENCES "NETWORK_CELL" ("CELLID", "NETOPID")

)

(of course there will be a Unique key on "NETWORK_CELL" ("CELLID", "NETOPID"))

In other words, an IRI will have a mandatory FK relationship with a Network Operator, and an optional FK relationship with a Network Cell.

The "suspect" thing is that this "optional" FK is composed by a mandatory field and an optional one, on IRI side.

Oracle RDBMS accepts this (I just tried), but is it a good practice?

Option 2:

Same FK, like in option 1, but leave IRI.NETOPID nullable and add a custom constraint that enforce either netOpId or netOpId+cellId

I feel this solution more portable, but maybe I'm wrong.

The question

Are there better options?

What's the best practice to deal with this situation and why? I'm thinking about portability to other RDBMS, too...

Thank you

Upvotes: 0

Views: 55

Answers (2)

philipxy
philipxy

Reputation: 15148

Your option 1 is OK. The way default FK (foreign key) declaration mode MATCH SIMPLE (usually the only one implemented) works, a FK subrow value with any NULLs satisfies its constraint. So you can have IRI FKs (netid) & (netid, cellid)--plus netid NOT NULL. (You seem to have forgotten the NOT NULL in your first IRI though not the second.)

Then the only cases for the column pair are (non-null, null) & (non-null, non-null). A netid must exist; a non-null cellid must exist with that netid & a NULL cellid is OK.

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

According to my understanding one solution could be this one:

CREATE TABLE IRI (
  IRIID INTEGER NOT NULL,
  NETOPID INTEGER,
  CELLID INTEGER,
  NAME VARCHAR2(20),
  CONSTRAINT IRI_PK PRIMARY KEY (IRIID),
  CONSTRAINT IRI_NETOPS_FK FOREIGN KEY (NETOPID) REFERENCES NETWORK_OPERATOR (NETOPID),
  CONSTRAINT IRI_CELLS_FK FOREIGN KEY (CELLID) REFERENCES NETWORK_CELL (CELLID),
  CONSTRAINT IRI_CELL_OR_NETOP CHECK ( NVL(NETOPID, CELLID) IS NOT NULL )
)

If you like to enforce that only of value is set you can use

CHECK ( NVL(NETOPID, CELLID) IS NOT NULL AND NOT (NETOPID IS NOT NULL AND CELLID IS NOT NULL) )

or

CHECK ( NVL(NETOPID, CELLID) IS NOT NULL AND NETOPID||CELLID IN (NETOPID, CELLID) )

or 

CHECK ( (NETOPID IS NULL AND CELLID IS NOT NULL) OR (NETOPID IS NOT NULL AND CELLID IS NULL) )

Upvotes: 0

Related Questions