Sql table where row may only be inserted if unique column

Question

I want to create a table which contains person, house and family, where only persons from the same family are allowed to live in the same house.

What I have so far does not work because I can only post one row with unique family and house. Is there any way to do this?

CREATE TABLE familyhouse (
  person VARCHAR(64),
  house VARCHAR(64),
  family VARCHAR(64),
  unique(house,family)
);

Example of correct table:

man,'1','1'
man2,'1','1'
man3,'1','1'
man4,'2','2'
man5,'2','2'
man6,'3','3'

Example of non-correct table:

man,'1','1'
man2,'1','1'
man3,'1','2'

Answer 1

I'd leverage the power of foreign keys and put the house and family in their own table (family_house) and a separate table for the residents.

CREATE TABLE family_house (
  house VARCHAR(128) NOT NULL UNIQUE,
  family VARCHAR(64) NOT NULL,
  PRIMARY KEY (house, family)
);

CREATE TABLE residents (
  person VARCHAR(64),
  house VARCHAR(128),
  family VARCHAR(64),
  UNIQUE (person, house, family),
  FOREIGN KEY (house, family) REFERENCES family_house
);

This way I can have multiple residents in the same home, but only one family to a home.