Unique constraint for single structure that simulates relations between tables

Question

Imagine I have several tables in SQL Server 2016:

1. Cat
2. Cat carrier
3. Cat transit box
4. Cat car

Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can be put into a carrier, the carrier into a transit box and the box put into a car. But a cat can simply ride in a car. Objects can store numerous types, so for example: Cat transit box can hold simultaneously cats and cat carriers.

There is no need for Cat or Cat Car in all this.

Now, the idea is to create a table joining everything at once:

Cat	Cat Carrier	Cat Box	Cat Car
null	3	5	null
null	4	5	null
1	5	5	null
2	null	5	null

So this is a situation where three carriers are put into a single box. one of those carriers contain a single cat. Also, there is a cat somewhere in the box.

But I have to make sure that there are no illogical data:

Cat	Cat Carrier	Cat Box	Cat Car
null	3	5	null
null	3	6	null
1	3	null	null
1	null	8	null

• Cat cannot be in different carriers.
• Cat cannot be in different cat boxes.
• Cat cannot be in different cat cars.
• Cat carrier cannot be in different cat boxes.
• Cat carrier cannot be in different cat cars.
• Cat box cannot be in different cat cars.
• (null is considered different to any specific value, so cat cannot be in cat carrier 3 and not be in any cat carrier at the same time. Two null values are ok).

Two important questions:

1. Is this flat structure adequate for what I want to do? Is there any better structure for this task?
2. How to create unique constraints to make sure an entity does not travel in multiple different containers.

Answer 1

I have a feeling you only care about cat in the car so why can't you put the cat in the car and make optional hierarchy of other container like

create table car (car_id int primary key)
create table cat (cat_id int primary key)
create table cat_carrier (cat_carrier_id int primary key)
create table cat_box (cat_box_id int primary key)
create table cat_car (car_id int not null references car,
                      cat_id int  not null references cat,
                      cat_carrier_id int null references cat_carrier,
                      cat_box_id int null references cat_box_id)

It's the simplest solution to start with.