| From: | Arda Çeşmecioğlu <arda(dot)mtb(at)gmail(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | more than just (m:n)? |
| Date: | 2012-03-06 20:56:55 |
| Message-ID: | 4F567A17.4030102@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
Hello,
I need a relationship between say table A and table B having primary
keys pkA and pkB respectively.
It seems like a (m:n) (if I did get the idea of this kind of
relationship right). So a table AtoB may be like;
CREATE TABLE "AtoB"(
"fk_pkA" integer NOT NULL REFERENCES "tblA" ("pkA"),
"fk_pkB" integer NOT NULL REFERENCES "tblB" ("pkB"),
PRIMARY KEY ("fk_pkA","fk_pkB")
);
What I want to accomplish is to have either 0, 1 or 3 (not 2 or more
than 3) of a particular fk_pkB value.
Now if I add another column and a CHECK;
CREATE TABLE "AtoB"(
"fk_pkA" integer NOT NULL REFERENCES "tblA" ("pkA"),
"fk_pkB" integer NOT NULL REFERENCES "tblB" ("pkB"),
"chkcol" text CHECK("chkcol" IN('a','b','c')),
PRIMARY KEY ("fk_pkA","fk_pkB")
UNIQUE("fk_pkB","chkcol")
);
This makes sure that no more than 3 rows with the same pkB value but
still allows for 2 rows.
Any ideas?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Merlin Moncure | 2012-03-06 22:04:06 | Re: Slow duplicate deletes |
| Previous Message | Daniel Staal | 2012-03-06 16:59:06 | Re: Database not browsable during COPY on PostgreSQL |