Constraint question

I have 'Table1' with two fields:
- RecID
- POID
I want to allow records to be entered in Table1 where the same RecID and
POID can exist across multiple records in Table1, but I don't want to allow
a POID to exist in Table1 twice with different RecIDs.
This is OK
RecID | POID
1 | 100
1 | 100
This is not OK
RecID | POID
1 | 100
2 | 100
What's the best way to set this up?
Thanks,
CB1. add another table with constraints PK(RecID, POID ) and
UNIQUE(POID) or vice versa
2. add a FK to table1 referencing that another table (RecID, POID )
P.S. there could be a problem with your database design. you might want
to post your DDL|||On Mon, 3 Oct 2005 13:28:22 -0400, Chris Burgess wrote:
(snip)
>This is OK
>RecID | POID
>1 | 100
>1 | 100
Hi Chris,
No it's not. Unless you have more columns in the table, but omitted them
in this post.
But with this design, you've now got two exact duplicates. And SQL gives
you no way to address just one of them. Every change you wish to make to
one of these rows will be made to the other as well.
If the reality you are modeling has two entities with RecID 1 and POID 1
and you've got a way to distinguish them, then add an extra column to
store whatever distinguishes the two in your business. If you've got the
both of them, they are really indistiguishable, but you still need to
store the fact that there are two of them, add a columns to hold the
current number. That solves your constraint problem as well:
CREATE TABLE YourTable
(RecID int NOT NULL,
POID int NOT NULL,
NumberOfItems int NOT NULL DEFAULT 1,
PRIMARY KEY (POID),
CHECK (NumberOfItems >= 1)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.