![]() The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. Note: The preferred way to add a unique constraint to a table is ALTER TABLE … ADD CONSTRAINT. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index. So I think it is what you call "partial uniqueness" by adding a constraint.Īdding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. ![]() The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table). If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. ĭETAIL: Cannot create a primary key or unique constraint using such an index. LINE 1: alter table master add constraint master_part_id_key unique. alter table master add column part_id integer Ĭreate unique index master_partial_idx on master (part_id) where part_id is not null Īlter table master add constraint master_part_id_key unique using index master_partial_idx ĮRROR: "master_partial_idx" is a partial index You cannot add table constraint using partial index. You can also create index on expression (not only on column) and define some other parameters (collation, sort order, NULLs placement). In unique index declaration you can set WHERE clause to create partial index. It comes directly from the definition of create table. In table constraint declaration you cannot create partial indexes. Now there is no difference between column constraints description. TABLE "detail" CONSTRAINT "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id) TABLE "detail" CONSTRAINT "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id) "master_ind_id_key" UNIQUE CONSTRAINT, btree (ind_id) alter table master add constraint master_ind_id_key unique using index master_unique_idx ![]() You can add table constraint using existing unique index. test=# insert into detail values (0, 0) ĮRROR: insert or update on table "detail" violates foreign key constraint "detail_fk1"ĭETAIL: Key (con_id)=(1) is not present in table "master".ĮRROR: insert or update on table "detail" violates foreign key constraint "detail_fk2"ĭETAIL: Key (ind_id)=(1) is not present in table "master".īoth columns can be referenced in foreign keys. "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id) "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id) create table detail (Ĭonstraint detail_fk1 foreign key (con_id) references master(con_id),Ĭonstraint detail_fk2 foreign key (ind_id) references master(ind_id) Now we'll define detail table with two foreign keys referencing to our two columns in master. test=# insert into master values (0, 0) ĮRROR: duplicate key value violates unique constraint "master_con_id_key"ĮRROR: duplicate key value violates unique constraint "master_unique_idx" In table description (\d in psql) you can tell unique constraint from unique index. "master_unique_idx" UNIQUE, btree (ind_id) "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id) create table master (Ĭreate unique index master_unique_idx on master (ind_id) Let's create test table master with two columns, con_id with unique constraint and ind_id indexed by unique index. I had some doubts about this basic but important issue, so I decided to learn by example.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |