0

I need to make table that seems to require two column primary key with one of the columns nullable.

But according to questions I read on Stack Overflow that is undesirable and even against SQL standards (for example: NULL value in multi-column primary key)

Yet I cannot figure out how to avoid it, and I would appreciate help. Here is the problem:

I need to track performance of various block that can fill space on page on e-commerce site, but some of those blocks have multiple configurations that I need to track separately and some do not.

I thought of using a two column PK, one column would be char(8) with block name, other would be ID of configuration that would contain either null for blocks that are immutable or the ID of the configuration from the respective block's configuration table (FK basically) for others.

How do I do this without a nullable column in PK?

(I already thought about adding config_id column that would be PK but then same two columns would have to be unique and that seem like getting nowhere to me.)

closed as unclear what you're asking by Ixrec, GlenH7, Robert Harvey, user40980, durron597 Nov 2 '15 at 0:47

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Can you put a special row in the block configuration table that means "immutable"? – Erik Eidt Oct 31 '15 at 20:26
  • @ErikEidt configuration table is different for each block. with different thing to configure. – Lukáš Rutar Oct 31 '15 at 20:28
  • So you can't put a configuration that say "no configuration"? – Erik Eidt Oct 31 '15 at 20:38
  • If I'm reading this right, the immutable blocks probably still have a configuration of some kind, even if you never change it. Why not store that instead of null? (if you need the database to distinguish between immutable and "only had one configuration", have a separate boolean column for an immutability flag) – Ixrec Oct 31 '15 at 20:39
  • Also, if there's a one-to-many relationship between blocks and configurations, maybe your configurations belong in a separate table from your blocks. – Ixrec Oct 31 '15 at 20:51
3

Use config id=0 instead of NULL as an indicator for "immutable blocks", and make sure each config record has an id>0. If you want to use the id column as a foreign key, create an artificial configuration record with id=0 (which your program interprets as "no configuration").

Technically (and independently from the former suggestion), it may be beneficial not to use the combined two-column key as the primary key directly, but to introduce an additional column "blockId" for your primary key. In fact, when I design a relational schema, I typically use an integer column of the form "TablenameID" for each table as the primary key, which makes a lot of things easier.

Note that the introduction of a blockId column alone will solve your original problem only on some databases - some db systems allow NULL values as part of a UNIQUE CONSTRAINT, others do not allow this, and some others allow this but do not guarantee uniqueness for records where one columns contain NULL. You might consider to check what your DB system is capable of, but I heavily recommend to use my initial suggestion to solve your problem in a database independent manner.

  • Are you suggesting a magic value? – Brandon Nov 1 '15 at 14:31
1

Your problem is one of an optional relationship between two entitites. Some blocks have multiple configurations and some do not. I'm taking that to mean that blocks that do not have multiple configurations have no configuration at all. If they have one configuration, your problem disappears.

You need three tables. One for blocks, one for configurations, and one for block-configurations. The third table represents the relationship, and contains two foreign keys. Its PK is the combination of the two foreign keys, taken together, if you want the relationship to be many-to-many. If not, then the PK can be one of the two fields.

When a given block has no relationship with any configuration, just omit the row in the third table. No problem! There aren't any nulls here, there's just an omitted row!

If you want to read more on this, look up "Sixth Normal Form".

  • "if you want the relationship to be many-to-many. If not, then the PK can be one of the two fields" - if it's 1-to-N, then you should not have a separate table, but just put an extra FK in one of the tables (the "one-" side). See my answer for example of what I mean. You solution is perfect for N-to-N, it allows to use the same configuration for more than one block, which might be a valid use case. – scriptin Nov 1 '15 at 12:08
  • You are right. I phrased my answer the way I did because the OP was using a composite key as a PK. That implies a many-to-many relationship, although the OP didn't say so. If it's many-or-zero to one, then the OP should not have had the problem to begin with. – Walter Mitty Nov 1 '15 at 12:27
0

Difficult to tell from your question, but I usualy find that every table should have a single col primary key.

Perhaps you actualy need to have a third col as a primary key for the table with the two exisiting cols as FKs

When you create a primary key out of multiple FKs you can find that it becomes non unique as your software evolves, or for example your page configuration changes over time.

  • It seems like you suggesting using something akin to a join table togother with a surrogate primary key ("a third col as a primary key for the table with the two exisiting cols as FKs"), which would effectively create an N-to-N association, which, I think, conflicts with asker's intention of creating 1-to-N association. Also, if you create a join table, you don't need a surrogate primary key in it at all, since there's no sense to make FK-columns nullable in this case (you just don't create a record if there's a null on either side of a corresponding association). – scriptin Nov 1 '15 at 10:01
  • No. Just an extra col, no tables – Ewan Nov 1 '15 at 10:33
  • Then, you have a problem of possible duplicates, unless you put additional unique key constraint on some other field(s), in which case you should just replace this unique key with a primary key, in which case you didn't need the surrogate PK altogether. The only "true" reasons for surrogate PK are (a) to have a smaller type to put an index upon (e.g. int vs long varchar) and (b) when you not sure if your natural PK is indeed unique and it's not going to change in the future. – scriptin Nov 1 '15 at 10:40
  • Im saying that the presence of the null in the origional PK inficates that you need the potential for duplicates – Ewan Nov 1 '15 at 10:53
  • My definition: a duplicate is a record which differs from some other record(s) just by it's surrogate PK. Why would you need that? – scriptin Nov 1 '15 at 10:55
0

I see two potential problems with your design:

  1. Reversing an association b/w blocks and their configurations by putting a foreign key at the wrong side of that association
  2. Having two subtypes of blocks (immutable and the ones with configurations) mixed in a single table - "single table inheritance"

It seems like you're trying to implement a standard 1-to-N association b/w blocks and block_configurations, but for some reason you decided to reverse it by putting the key of config into each block, while you should do the opposite, i.e. you should put block_id into block_configurations instead of putting configuration_id into blocks:

CREATE TABLE blocks (
  block_id CHAR(10),
  is_immutable BIT(1) NOT NULL,
  PRIMARY KEY (block_id)
);

CREATE TABLE block_configurations (
  configuration_id INT(10) AUTO_INCREMENT,
  block_id CHAR(10) NOT NULL,
  PRIMARY KEY (configuration_id),
  FOREIGN KEY block_id_fk(block_id) REFERENCES blocks(block_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

The only thing that is not enforced by this schema is the fact of immutable blocks must not have associated configuration records. You can do that using triggers, but the better solution is to use separate configurable_blocks and immutable_blocks tables, since in this case there are two subtypes of blocks. There are two ways to do that: "multiple table inheritance" and "class table inheritance". The one I showed above is a "single table inheritance", where two subtypes of blocks are stored within a single table.

Here's an example with multiple table inheritance:

CREATE TABLE immutable_blocks (
  block_id CHAR(10),
  PRIMARY KEY (block_id)
);

CREATE TABLE configurable_blocks (
  block_id CHAR(10),
  PRIMARY KEY (block_id)
);

CREATE TABLE block_configurations (
  configuration_id INT(10) AUTO_INCREMENT,
  block_id CHAR(10) NOT NULL,
  PRIMARY KEY (configuration_id),
  FOREIGN KEY configurable_block_id_fk(block_id) 
    REFERENCES configurable_blocks(block_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

This design has another flaw: if you have common attributes in two subtypes of blocks, you have to duplicate them across two tables. That's where the class table inheritance shines:

CREATE TABLE blocks (
  block_id CHAR(10),
  description VARCHAR(100), -- common field
  PRIMARY KEY (block_id)
);

CREATE TABLE immutable_blocks (
  block_id CHAR(10),
  FOREIGN KEY block_id_fk(block_id) REFERENCES blocks(block_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

CREATE TABLE configurable_blocks (
  block_id CHAR(10),
  FOREIGN KEY block_id_fk(block_id) REFERENCES blocks(block_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

CREATE TABLE block_configurations (
  ... same here, still references only configurable_blocks ...
);

This design ensures referential integrity and allows you to separate common and subtype-specific fields.

Related reading:

  • SQL Antipatterns, "Polymorphic Associations" chapter - solutions in this chapter apply to this case
  • SQL Antipatterns, "Entity-Attribute-Value" chapter - describes single/multiple/class table inheritance, related to a problem of having subtypes of some entity

Not the answer you're looking for? Browse other questions tagged or ask your own question.