New answers tagged unique-key
You cannot directly create a unique constraint across tables. I say directly because you could come up with some indirect scheme; what comes to my mind would be using triggers to insert into a third table that has the unique constraint. But that's a lot of extra work compared to the obvious solution, which is to keep the column "a" in the "my" table.
You need to remove the status column from your index: CREATE UNIQUE INDEX builders_unique_house ON houses (builder_id) WHERE status IN ('PLANNING', 'CONSTRUCTING'); If you keep the status column, the index will contain e.g. ('PLANNING', 1) and ('CONSTRUCTING', 1) - that combination is unique. If you remove the status, only the builder_id will be ...
Since the Stack Overflow post you linked to explains how the unique key constraint is implemented using a unique index, so the two are the same for practical purposes, I'll skip that part and try to explain how a unique index improves query performance. Let's stick to your example where table named myTable has a column named id which happens to be filled ...
The optimizer is able to use the knowledge that a column is unique when deciding how to retrieve rows predicated on that column. It may produce a different, more efficient, execution plan with the unique constraint in place than without it. Without the unique constraint the optimizer has to interpolate from the statistics object created on that column, ...
A unique constraint will create an index. So, when in your query there is: where id=15 An index will drastically improve your query. With no index you will read all the table.
Top 50 recent answers are included