What does a CONSTRAINT have to do with my unique index?
I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change my CREATE INDEX statement to add that to it.
"customers_phonetype_customer_id_176731583d230ba5_uniq" UNIQUE CONSTRAINT, btree (customer_id, display) ;-- Django created
"customers_phonetype_customer_id_uniq" UNIQUE, btree (customer_id, lower(display::text)) ; -- Manually created
This is how I manually created the second index:
create unique index customers_phonetype_customer_id_uniq on customers_phonetype (customer_id, lower(display));
What is the meaning of the extra word CONSTRAINT in this case? I'm not seeing it in the docs.
postgresql database-design index constraint ddl
edited Mar 12 '15 at 4:10
Erwin Brandstetter
93.5k9180293
asked Mar 12 '15 at 3:43
boatcoderboatcoder
243210
add a comment |
I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change my CREATE INDEX statement to add that to it.
"customers_phonetype_customer_id_176731583d230ba5_uniq" UNIQUE CONSTRAINT, btree (customer_id, display) ;-- Django created
"customers_phonetype_customer_id_uniq" UNIQUE, btree (customer_id, lower(display::text)) ; -- Manually created
This is how I manually created the second index:
create unique index customers_phonetype_customer_id_uniq on customers_phonetype (customer_id, lower(display));
What is the meaning of the extra word CONSTRAINT in this case? I'm not seeing it in the docs.
postgresql database-design index constraint ddl
edited Mar 12 '15 at 4:10
Erwin Brandstetter
93.5k9180293
asked Mar 12 '15 at 3:43
boatcoderboatcoder
243210
add a comment |
I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change my CREATE INDEX statement to add that to it.
"customers_phonetype_customer_id_176731583d230ba5_uniq" UNIQUE CONSTRAINT, btree (customer_id, display) ;-- Django created
"customers_phonetype_customer_id_uniq" UNIQUE, btree (customer_id, lower(display::text)) ; -- Manually created
This is how I manually created the second index:
create unique index customers_phonetype_customer_id_uniq on customers_phonetype (customer_id, lower(display));
What is the meaning of the extra word CONSTRAINT in this case? I'm not seeing it in the docs.
postgresql database-design index constraint ddl
edited Mar 12 '15 at 4:10
Erwin Brandstetter
93.5k9180293
asked Mar 12 '15 at 3:43
boatcoderboatcoder
243210
I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change my CREATE INDEX statement to add that to it.
"customers_phonetype_customer_id_176731583d230ba5_uniq" UNIQUE CONSTRAINT, btree (customer_id, display) ;-- Django created
"customers_phonetype_customer_id_uniq" UNIQUE, btree (customer_id, lower(display::text)) ; -- Manually created
This is how I manually created the second index:
create unique index customers_phonetype_customer_id_uniq on customers_phonetype (customer_id, lower(display));
What is the meaning of the extra word CONSTRAINT in this case? I'm not seeing it in the docs.
postgresql database-design index constraint ddl
postgresql database-design index constraint ddl
edited Mar 12 '15 at 4:10
Erwin Brandstetter
93.5k9180293
asked Mar 12 '15 at 3:43
boatcoderboatcoder
243210
edited Mar 12 '15 at 4:10
Erwin Brandstetter
93.5k9180293
asked Mar 12 '15 at 3:43
boatcoderboatcoder
243210
edited Mar 12 '15 at 4:10
Erwin Brandstetter
93.5k9180293
edited Mar 12 '15 at 4:10
Erwin Brandstetter
93.5k9180293
edited Mar 12 '15 at 4:10
Erwin Brandstetter
93.5k9180293
93.5k9180293
asked Mar 12 '15 at 3:43
boatcoderboatcoder
243210
asked Mar 12 '15 at 3:43
boatcoderboatcoder
243210
asked Mar 12 '15 at 3:43
boatcoderboatcoder
243210
243210
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The first one is a unique constraint. It can be added to an existing table with:
ALTER TABLE ADD CONSTRAINT ...
Details in the manual here.
It is implemented using a unique index. Per documentation:
Adding a unique constraint will automatically create a unique btree
index on the column or group of columns used in the constraint. A
uniqueness constraint on only some rows can be enforced by creating a partial index.
In addition it allows foreign key references to it.
The second one in a unique index.
It couldn't be a unique constraint because those only allow columns, not expressions. More details:
How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Discussion on pgsql-general about the difference between constraint and index.
edited 17 mins ago
Pang
1216
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f95038%2fwhat-does-a-constraint-have-to-do-with-my-unique-index%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
The first one is a unique constraint. It can be added to an existing table with:
ALTER TABLE ADD CONSTRAINT ...
Details in the manual here.
It is implemented using a unique index. Per documentation:
Adding a unique constraint will automatically create a unique btree
index on the column or group of columns used in the constraint. A
uniqueness constraint on only some rows can be enforced by creating a partial index.
In addition it allows foreign key references to it.
The second one in a unique index.
It couldn't be a unique constraint because those only allow columns, not expressions. More details:
How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Discussion on pgsql-general about the difference between constraint and index.
edited 17 mins ago
Pang
1216
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
add a comment |
The first one is a unique constraint. It can be added to an existing table with:
ALTER TABLE ADD CONSTRAINT ...
Details in the manual here.
It is implemented using a unique index. Per documentation:
Adding a unique constraint will automatically create a unique btree
index on the column or group of columns used in the constraint. A
uniqueness constraint on only some rows can be enforced by creating a partial index.
In addition it allows foreign key references to it.
The second one in a unique index.
It couldn't be a unique constraint because those only allow columns, not expressions. More details:
How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Discussion on pgsql-general about the difference between constraint and index.
edited 17 mins ago
Pang
1216
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
add a comment |
The first one is a unique constraint. It can be added to an existing table with:
ALTER TABLE ADD CONSTRAINT ...
Details in the manual here.
It is implemented using a unique index. Per documentation:
Adding a unique constraint will automatically create a unique btree
index on the column or group of columns used in the constraint. A
uniqueness constraint on only some rows can be enforced by creating a partial index.
In addition it allows foreign key references to it.
The second one in a unique index.
It couldn't be a unique constraint because those only allow columns, not expressions. More details:
How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Discussion on pgsql-general about the difference between constraint and index.
edited 17 mins ago
Pang
1216
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
The first one is a unique constraint. It can be added to an existing table with:
ALTER TABLE ADD CONSTRAINT ...
Details in the manual here.
It is implemented using a unique index. Per documentation:
Adding a unique constraint will automatically create a unique btree
index on the column or group of columns used in the constraint. A
uniqueness constraint on only some rows can be enforced by creating a partial index.
In addition it allows foreign key references to it.
The second one in a unique index.
It couldn't be a unique constraint because those only allow columns, not expressions. More details:
How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Discussion on pgsql-general about the difference between constraint and index.
edited 17 mins ago
Pang
1216
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
edited 17 mins ago
Pang
1216
edited 17 mins ago
Pang
1216
edited 17 mins ago
Pang
1216
1216
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
93.5k9180293
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f95038%2fwhat-does-a-constraint-have-to-do-with-my-unique-index%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
