Thread: Constraint Syntax Question
I have a many-to-many relationship between A & B where a B *must* relate to at least 1 A but an A may have zero Bs. How do add this using an ALTER TABLE statement? Thanks, A. -- Adam Sherman Tritus CG Inc. http://www.tritus.ca/ +1 (613) 797-6819
On Sunday 11 May 2003 23:17, Adam Sherman wrote: > I have a many-to-many relationship between A & B where a B *must* relate to > at least 1 A but an A may have zero Bs. > > How do add this using an ALTER TABLE statement? You can't postgres enforces that the foreign key must be unique. so ALTER TABLE B ADD CONSTRAINT bafk FOREIGN KEY (key) REFERENCES A(key); looks like it should work by A(key) must be unique so you will get an error. I think that this is probably bad database design and you need to get you database into 1st normal form hense getting read of any many to many relations by removing any many to many relations. Find a good book on database design.I hope that helps Peter Childs > > Thanks, > > A. > > -- > Adam Sherman > Tritus CG Inc. > http://www.tritus.ca/ > +1 (613) 797-6819 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Sunday 11 May 2003 23:17, Adam Sherman wrote: > I have a many-to-many relationship between A & B where a B *must* relate to > at least 1 A but an A may have zero Bs. > > How do add this using an ALTER TABLE statement? You can't postgres enforces that the foreign key must be unique. so ALTER TABLE B ADD CONSTRAINT bafk FOREIGN KEY (key) REFERENCES A(key); looks like it should work by A(key) must be unique so you will get an error. I think that this is probably bad database design and you need to get you database into 1st normal form hense getting read of any many to many relations by removing any many to many relations. Find a good book on database design.I hope that helps Peter Childs > > Thanks, > > A. > > -- > Adam Sherman > Tritus CG Inc. > http://www.tritus.ca/ > +1 (613) 797-6819 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)