Question: (Rails 4.2.1, Sqlite3) I have three models - M1, M2, M3. 

M1 belongs\_to M2

M1 also belongs\_to M3

M1 has a :name (string) field. 

I have the following constraints that must be validated:

1) An M1 record can have an M2 or M3 associated, but not both.

2) M1 name must be unique under whichever of M2 or M3 is specified.

I have implemented constraint (1) in the model, and it works as expected. (I mention it only because it may be relevant to the scenario).

For constraint (2), I added an index in a migration as follows:

```
add_index :m1s, [:name, :m2_id, :m3_id], unique: true, name: "idx_m1_name"

```

Then I call:

```
> m2 = M2.create! # success
> m1_1 = M1.create!(name: 'm1_1', m2: m2) #success
> m1_2 = M1.create!(name: 'm1_1', m2: m2) # this line should fail, but doesn't

```

m1\_1 and m1\_2 get created - I expect that m1\_2 should fail due to the uniqueness constraint. 

I checked that the index does get added as expected. Also, as per constraint 1, m3\_id is nil in both m1\_1 and m1\_2, not sure if it is relevant.

Why is the constraint not being checked?

Answer: 
So `m3_id` is `NULL` in both cases ? In Sqlite3, a null value is considered distinct to another null value in the context of a unique index. 

> 
> For the purposes of unique indices, all NULL values are considered to
>  different from all other NULL values and are thus unique
> 
> 
> 

See <https://sqlite.org/lang_createindex.html>

I think it's the same in MySQL and Postgres too.
You can express this with two indices.

```rb
add_index :m1s, [:name, :m2_id], unique: true
add_index :m1s, [:name, :m3_id], unique: true

```

Since nulls are considered distinct for uniqueness purposes, the first index will not constrain rows where only `m3_id` is set, and vice versa for the second index.

(You might also be interested in a [`CHECK` constraint](http://www.w3schools.com/sql/sql_check.asp) that checks that exactly one of `m2_id` or `m3_id` is null. In Rails, `CHECK` constraints have the minor caveat of [requiring the schema file to be expressed in SQL](http://edgeguides.rubyonrails.org/active_record_migrations.html#reverting-previous-migrations). For a more general solution to "indices that only apply to some rows", see [partial indices](http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_index-label-Creating+a+partial+index).)