What does a CONSTRAINT have to do with my unique index?Get result from select exists, execute insert query...
SET NOCOUNT Error in handling SQL call after upgrade
How can my powered armor quickly replace its ceramic plates?
Non-Cancer terminal illness that can affect young (age 10-13) girls?
Words and Words with "ver-" Prefix
What would be the rarity of this magic item(s)?
How can a school be getting an epidemic of whooping cough if most of the students are vaccinated?
Odd 74HCT1G125 behaviour
When can a QA tester start his job?
Why exactly do action photographers need high fps burst cameras?
What are "industrial chops"?
Why avoid shared user accounts?
A Missing Symbol for This Logo
Do authors have to be politically correct in article-writing?
Play Zip, Zap, Zop
Is there a Linux system call to create a “view” of a range of a file?
Graph with overlapping labels
using 'echo' & 'printf' in bash function calls
Is there any risk in sharing info about technologies and products we use with a supplier?
A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?
How much mayhem could I cause as a sentient fish?
How do you funnel food off a cutting board?
How can I play a serial killer in a party of good PCs?
Numbers with a minus sign in a matrix not aligned with the numbers without minus sign
Why do neural networks need so many training examples to perform?
What does a CONSTRAINT have to do with my unique index?
Get result from select exists, execute insert query based on the response of itSQL Server : explicitly create an index on a primary key and unique fieldsMicrosoft Access - performance difference between index and constraint?Unique index for subquery with RANK() functionCreate index on very large table with many shared valuesClarify unique constraint and index, cannot grasp the concepts to 100%Postgresql - Do Foreign Key Constraints Automatically Create Indexes?MySQL performance of applying unique constraint single column vs combination columnIs unique index better than unique constraint when an index with an operator class is requiredUsing varchar_pattern_ops in a multi-column index in PostgresPostgreSQL - Enforcing unique constraint on date column parts
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 13 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 13 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 13 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 13 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 13 mins ago
Pang
1216
answered Mar 12 '15 at 4:08
Erwin BrandstetterErwin Brandstetter
93.5k9180293
edited 13 mins ago
Pang
1216
edited 13 mins ago
Pang
1216
edited 13 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
