Constrains relationship

I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him.

I want to insure that the host you assign to the domain belongs to the user that domain is assigned to.

Is it something that I have to insure in my application logic?

If I set domains.user_id foreign key to reference host.user_id and user_id.id does it eliminate my issue?

By the way can anyone suggest me a better design for my schema?

This is a rather common problem, when the design has a "diamond" shape. See similar questions:

Many to Many and Weak Entities

With MySQL, I'd use something like this:

(0) Note that I prefer user_id as name for the primary key of users and not id for all the tables. I find the SQL code totally confusing otherwise (plus you can use the JOIN ... USING (tablename_id) syntax).

(1) The extra UNIQUE constraints in tables hosts and contacts are needed for the foreign keys from the domains table.

(2) The two FOREIGN KEY constraints from the domains table are changed to use composite keys (include the user_id).

Table users

CREATE TABLE users
( user_id INT NOT NULL AUTO_INCREMENT
, username VARCHAR(45) NOT NULL
-- other columns
, PRIMARY KEY (user_id)
) ;

Table contacts

CREATE TABLE contacts
( contact_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, info TEXT
, PRIMARY KEY (contact_id)
, UNIQUE INDEX (user_id, contact_id)       -- added, see comment 1 above
, INDEX (user_id)
, FOREIGN KEY (user_id)
    REFERENCES users (user_id)
) ;

Table hosts

CREATE TABLE hosts
( host_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, name VARCHAR(45) NOT NULL
--
, PRIMARY KEY (host_id)
, UNIQUE INDEX (user_id, host_id)          -- added, see comment 1 above
, INDEX (user_id)
, FOREIGN KEY (user_id)
    REFERENCES users (user_id)
) ;

Table domains

CREATE TABLE domains
( domain_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, contact_id INT NOT NULL
, host_id INT NULL                       -- nullable based on comments
, name VARCHAR(45) NOT NULL
-- other columns
, PRIMARY KEY (domain_id)
, FOREIGN KEY (user_id, contact_id)       -- composite FK, see comment 2
    REFERENCES contacts (user_id, contact_id)
, FOREIGN KEY (user_id, host_id)          -- composite FK, see comment 2
    REFERENCES hosts (user_id, host_id)
) ;

StackExchange Database Administrators Q#49513, answer score: 6