0

Right now I have two tables, one that contains a compound primary key and another that that references one of the values of the primary key but is a one-to-many relationship between Product and Mapping. The following is an idea of the setup:

CREATE TABLE dev."Product"
(
  "Id" serial NOT NULL,
  "ShortCode" character(6),
  CONSTRAINT "ProductPK" PRIMARY KEY ("Id")
)

CREATE TABLE dev."Mapping"
(
  "LookupId" integer NOT NULL,
  "ShortCode" character(6) NOT NULL,
  CONSTRAINT "MappingPK" PRIMARY KEY ("LookupId", "ShortCode")
)

Since the ShortCode is displayed to the user as a six character string I don't want to have a another table to have a proper foreign key reference but trying to create one with the current design is not allowed by PostgreSQL. As such, how can I create a check so that the short code in the Mapping table is checked to make sure it exists?

Community
  • 1
  • 1
rjzii
  • 14,236
  • 12
  • 79
  • 119
  • Please *always* provide your version of Postgres, especially with tricky db design questions. – Erwin Brandstetter Sep 04 '14 at 19:42
  • A "1:M relationship between Product and Mapping" means Product is on the "one" side, and Mapping is on the "many" side. Is that really what you meant to say? – Mike Sherrill 'Cat Recall' Sep 04 '14 at 19:44
  • 1
    It's really unclear what's supposed to reference what and why neither "Product"."ShortCode" nor "Mapping"."ShortCode" can be declared `UNIQUE`. Your sentence `table ... is a one-to-many relationship between ...` does not make sense. – Erwin Brandstetter Sep 04 '14 at 22:09
  • 1
    @rjzii: If it (which table?!) implements an n:m relationship, then we are dealing with at least three tables, but you only give two. Please clarify the whole thing. – Erwin Brandstetter Sep 05 '14 at 01:56
  • For just that, consider the answer I already gave. But you really should revise your design. Building on a broken design will be a big, constant pain. A painful break is better than continued agony. Here is a complete code example for a proper n:m relationship: http://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql/9790225#9790225 And you still did not provide your version of Postgres, which should be the first, simple thing on your list. – Erwin Brandstetter Sep 05 '14 at 02:07

2 Answers2

0

Depending on the fine print of your requirements and your version of Postgres I would suggest a TRIGGER or a NOT VALID CHECK constraint.

We have just discussed the matter in depth in this related question on dba.SE:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
-1

If I understand you correctly, you need a UNIQUE constraint on "Product"."ShortCode". Surely it should be declared NOT NULL, too.

CREATE TABLE dev."Product"
(
  "Id" serial NOT NULL,
  "ShortCode" character(6) NOT NULL UNIQUE,
  CONSTRAINT "ProductPK" PRIMARY KEY ("Id")
);

CREATE TABLE dev."Mapping"
(
  "LookupId" integer NOT NULL,
  "ShortCode" character(6) NOT NULL REFERENCES dev."Product" ("ShortCode"),
  CONSTRAINT "MappingPK" PRIMARY KEY ("LookupId", "ShortCode")
);

Your original "Product" table will allow this INSERT statement to succeed, but it shouldn't.

insert into dev."Product" ("ShortCode") values
(NULL), (NULL), ('ABC'), ('ABC'), ('ABC');

Data like that is just about useless.

select * from dev."Product"

id  ShortCode
--
1   
2   
3   ABC   
4   ABC   
5   ABC
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Sorry if the question wasn't clear enough, but `Product.ShortCode` has a one-to-many relationship on `Mapping.ShortCode` so the `UNIQUE` constraint wouldn't work. The example that you have with the `NULL` is what we are trying to avoid. Kind of a weird schema I know. – rjzii Sep 04 '14 at 19:33
  • The code I wrote has a one-to-many relationship from Product.ShortCode to Mapping.ShortCode. – Mike Sherrill 'Cat Recall' Sep 04 '14 at 19:42