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?

Improve this question
5
  • Please always provide your version of Postgres, especially with tricky db design questions. Commented Sep 4, 2014 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? Commented Sep 4, 2014 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. Commented Sep 4, 2014 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. Commented Sep 5, 2014 at 1: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: stackoverflow.com/questions/9789736/… And you still did not provide your version of Postgres, which should be the first, simple thing on your list. Commented Sep 5, 2014 at 2:07

2 Answers 2

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-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
Improve this answer

2 Comments

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.
The code I wrote has a one-to-many relationship from Product.ShortCode to Mapping.ShortCode.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.