Constraints and optimal table design

Question

Here's my design as is

I want a constraint that will ensure only (at most) one result of

select ID 
  from A a, B b 
 where a.ID = b.PartialKey_Ref_A
       and a.PartCandidateB = 'valueA' 
       and b.PartialKeyB = 'valueB'

Incidentally (perhaps changes the optimal design) I want at most one result from

select ID 
  from A 
 where PartCandidateA = 'valueA2' 
       and PartCandidateB = 'valueB2'

How can I enforce the constraint and optimize the design?

Answer 1

I think I need to add the PartCandidateB column to Table B. Then I can add the unique constraint on (PartialKeyB,PartCandidateB). This will increase the DB by sizeof(PartCandidateB)*rows in TableB. But the constraint will be enforced:)

I don't think this introduces any problems other than the size increase thing

Thanks to everyone

Answer 2

I assume that where you write Key, you mean Unique or Primary Key. And that ID means a surrogate (auto-generated) identifier. With these assumptions, the two tables are in 1:n relationship and you could change them into:

Table A
-------
PartCandidateA
PartCandidateB
ID
PRIMARY KEY (ID)
UNIQUE KEY (PartCandidateA, PartCandidateB)    --- or PRIMARY if you drop the ID
                                               --- this is your second constraint


Table B
-------
PartCandidateA
PartCandidateB
PartialKeyB
PRIMARY KEY (PartCandidateB, PartialKeyB)      --- or UNIQUE
                                               --- this is your first constraint
FOREIGN KEY (PartCandidateA, PartCandidateB)
  REFERENCES A (PartCandidateA, PartCandidateB) 

---

So, your query to find the ID will be written as:

SELECT ID 
  FROM A a, B b 
 WHERE a.PartCandidateA = b.PartCandidateA
   AND a.PartCandidateB = b.PartCandidateB
   AND b.PartCandidateB = 'valueA' 
   AND b.PartialKeyB = 'valueB'

Answer 3

You can simply create a unique contraint on those two columns by creating a unique index over them:

create unique index ind1 on tablea(PartCandidateA, PartCandidateB);