Hey Could you write specific functions "insert"/"update" that people use when they update the data in the db, that checks for the constraints you are talking about. So the functions would take in the input data and then would scan the table to make sure there is no "book" with the same "isbn" that also has different name, if it did your function could raise an error message. I realise you said you actually wanted to put the constraint on the table, but I just thought I would suggest this anyway. Cheers Adam Adam Lawrence Mediasculpt Direct Line: +64 6 3546038 Email: adam@xxxxxxxxxxxxxxx ----- Original Message ----- From: "Miles Keaton" <mileskeaton@xxxxxxxxx> To: <pgsql-general@xxxxxxxxxxxxxx> Sent: Monday, October 10, 2005 4:25 PM Subject: Re: brain-teaser with CONSTRAINT - any SQL experts? > > I would create a multi-column unique index on the table. This should solve > > the problem mentioned although you may still have an integrity issue if a > > "book" name is mistyped. > > Hm? > > This sounds promising, except it's the exact opposite of what I need. > > Is this what you meant? > > CREATE TABLE lineitems (code int, name varchar(12), UNIQUE (code, name)); > > Because that breaks the whole idea where I should be able to have many > lines with the same item: > > insert into lineitems(code, name) VALUES (123, 'bob'); > INSERT 35489 1 > insert into lineitems(code, name) VALUES (123, 'bob'); > ERROR: duplicate key violates unique constraint "lineitems_code_key" > > What I want is for that situation, above, to NOT make an error. > But this, below, should: > > insert into lineitems(code, name) VALUES (123, 'bob'); > insert into lineitems(code, name) VALUES (123, 'xxx'); > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
