Mixing
Doesn't Postgres reuse unique indexes for unique key constraint?
Having experience with Oracle I assumed that each unique constraint would reuse unique index.
I created schema population script that creates named unique index and then same unique constraint. In that way I hoped to set index name explicitly rather than relay on Postgres default naming schema.
As experiment was shown I got two indexes with same definition in a result:
CREATE UNIQUE INDEX agent_ux ON agent (branch_id, initials);
ALTER TABLE agent ADD CONSTRAINT agent_uk UNIQUE (branch_id, initials);
select indexname from pg_indexes where tablename = 'agent';
agent_ux
agent_uk
Doesn't Postgres reuse unique indexes for unique key constraint?
NOTE I can't drop index, corresponding to unique constraint (error says about related constraint), but index is automatically deleted if I delete constraint.
postgresql
asked Jan 2 at 16:14
gavenkoagavenkoa
23.4k11146187
add a comment |
Having experience with Oracle I assumed that each unique constraint would reuse unique index.
I created schema population script that creates named unique index and then same unique constraint. In that way I hoped to set index name explicitly rather than relay on Postgres default naming schema.
As experiment was shown I got two indexes with same definition in a result:
CREATE UNIQUE INDEX agent_ux ON agent (branch_id, initials);
ALTER TABLE agent ADD CONSTRAINT agent_uk UNIQUE (branch_id, initials);
select indexname from pg_indexes where tablename = 'agent';
agent_ux
agent_uk
Doesn't Postgres reuse unique indexes for unique key constraint?
NOTE I can't drop index, corresponding to unique constraint (error says about related constraint), but index is automatically deleted if I delete constraint.
postgresql
asked Jan 2 at 16:14
gavenkoagavenkoa
23.4k11146187
@wildplasser Can you write answer so I accept it?
– gavenkoa
Jan 2 at 17:02
add a comment |
Having experience with Oracle I assumed that each unique constraint would reuse unique index.
I created schema population script that creates named unique index and then same unique constraint. In that way I hoped to set index name explicitly rather than relay on Postgres default naming schema.
As experiment was shown I got two indexes with same definition in a result:
CREATE UNIQUE INDEX agent_ux ON agent (branch_id, initials);
ALTER TABLE agent ADD CONSTRAINT agent_uk UNIQUE (branch_id, initials);
select indexname from pg_indexes where tablename = 'agent';
agent_ux
agent_uk
Doesn't Postgres reuse unique indexes for unique key constraint?
NOTE I can't drop index, corresponding to unique constraint (error says about related constraint), but index is automatically deleted if I delete constraint.
postgresql
asked Jan 2 at 16:14
gavenkoagavenkoa
23.4k11146187
Having experience with Oracle I assumed that each unique constraint would reuse unique index.
I created schema population script that creates named unique index and then same unique constraint. In that way I hoped to set index name explicitly rather than relay on Postgres default naming schema.
As experiment was shown I got two indexes with same definition in a result:
CREATE UNIQUE INDEX agent_ux ON agent (branch_id, initials);
ALTER TABLE agent ADD CONSTRAINT agent_uk UNIQUE (branch_id, initials);
select indexname from pg_indexes where tablename = 'agent';
agent_ux
agent_uk
Doesn't Postgres reuse unique indexes for unique key constraint?
NOTE I can't drop index, corresponding to unique constraint (error says about related constraint), but index is automatically deleted if I delete constraint.
postgresql
postgresql
asked Jan 2 at 16:14
gavenkoagavenkoa
23.4k11146187
asked Jan 2 at 16:14
gavenkoagavenkoa
23.4k11146187
asked Jan 2 at 16:14
gavenkoagavenkoa
23.4k11146187
asked Jan 2 at 16:14
gavenkoagavenkoa
23.4k11146187
asked Jan 2 at 16:14
gavenkoagavenkoa
23.4k11146187
23.4k11146187
@wildplasser Can you write answer so I accept it?
– gavenkoa
Jan 2 at 17:02
add a comment |
@wildplasser Can you write answer so I accept it?
– gavenkoa
Jan 2 at 17:02
@wildplasser Can you write answer so I accept it?
– gavenkoa
Jan 2 at 17:02
@wildplasser Can you write answer so I accept it?
– gavenkoa
Jan 2 at 17:02
add a comment |
1 Answer
1
active
oldest
votes
In postgres, creating a UNIQUE constraint automatically creates an index. You can also create the constraint by promoting an existing index, using the ALTER TABLE ttt add constraint ccc USING xxx syntax: Documentation
ALTER TABLE agent
ADD CONSTRAINT agent_uk UNIQUE USING agent_ux;
[untested]
answered Jan 2 at 17:15
wildplasserwildplasser
31.7k64472
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009615%2fdoesnt-postgres-reuse-unique-indexes-for-unique-key-constraint%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
In postgres, creating a UNIQUE constraint automatically creates an index. You can also create the constraint by promoting an existing index, using the ALTER TABLE ttt add constraint ccc USING xxx syntax: Documentation
ALTER TABLE agent
ADD CONSTRAINT agent_uk UNIQUE USING agent_ux;
[untested]
answered Jan 2 at 17:15
wildplasserwildplasser
31.7k64472
add a comment |
In postgres, creating a UNIQUE constraint automatically creates an index. You can also create the constraint by promoting an existing index, using the ALTER TABLE ttt add constraint ccc USING xxx syntax: Documentation
ALTER TABLE agent
ADD CONSTRAINT agent_uk UNIQUE USING agent_ux;
[untested]
answered Jan 2 at 17:15
wildplasserwildplasser
31.7k64472
add a comment |
In postgres, creating a UNIQUE constraint automatically creates an index. You can also create the constraint by promoting an existing index, using the ALTER TABLE ttt add constraint ccc USING xxx syntax: Documentation
ALTER TABLE agent
ADD CONSTRAINT agent_uk UNIQUE USING agent_ux;
[untested]
answered Jan 2 at 17:15
wildplasserwildplasser
31.7k64472
In postgres, creating a UNIQUE constraint automatically creates an index. You can also create the constraint by promoting an existing index, using the ALTER TABLE ttt add constraint ccc USING xxx syntax: Documentation
ALTER TABLE agent
ADD CONSTRAINT agent_uk UNIQUE USING agent_ux;
[untested]
answered Jan 2 at 17:15
wildplasserwildplasser
31.7k64472
answered Jan 2 at 17:15
wildplasserwildplasser
31.7k64472
answered Jan 2 at 17:15
wildplasserwildplasser
31.7k64472
answered Jan 2 at 17:15
wildplasserwildplasser
31.7k64472
31.7k64472
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009615%2fdoesnt-postgres-reuse-unique-indexes-for-unique-key-constraint%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
@wildplasser Can you write answer so I accept it?
– gavenkoa
Jan 2 at 17:02