0

If I have a person table, with 2 fields (name and address). 

create table PERSON  (
NAME                 VARCHAR2(50),
ADDRESS              VARCHAR2(100)  
);

How do I ensure that all people with the same address have different names.

I was trying to use CHECK constraint, there are some examples on the web that use sub-queries inside the create table statement, but I got an SQL Error:

ORA-02251: subquery not allowed here

Any helps would be appreciated. Thanks

Alex Hadley
  • 2,125
  • 2
  • 28
  • 50
Jeffry Tanudjaja
  • 1
  • A unique index on name, address should do. So you can't insert the same combination of name/address twice. Which on the other hand means that all names for the same address have to be different. –  May 17 '12 at 11:18

1 Answers1

1

You can not leave the row in check constraint. You can use triggers for this purpose.

Val Bakhtin
  • 1,434
  • 9
  • 11
  • Me thinks that a unique constraint (as per @a_horse_with_no_name's comment) is cleaner than a kludge of triggers. – wildplasser May 17 '12 at 11:21