Sfrgttk

How does Leonard in "Memento" remember reading and writing?

Why did Democrats in the Senate oppose the Born-Alive Abortion Survivors Protection Act (2019 S.130)?

Move fast ...... Or you will lose

Difference between i++ and (i)++ in C

Numbers with a minus sign in a matrix not aligned with the numbers without minus sign

Making him into a bully (how to show mild violence)

Early credit roll before the end of the film

In Linux what happens if 1000 files in a directory are moved to another location while another 300 files were added to the source directory?

Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?

What does it mean for a caliber to be flat shooting?

Eww, those bytes are gross

What sets the resolution of an analog resistive sensor?

How do I append a character to the end of every line in an Excel cell?

Convert exam marks to overall grade

It took me a lot of time to make this, pls like. (YouTube Comments #1)

If I delete my router's history can my ISP still provide it to my parents?

Is it possible to grant users sftp access without shell access? If yes, how is it implemented?

A Missing Symbol for This Logo

Why is Agricola named as such?

Non-Cancer terminal illness that can affect young (age 10-13) girls?

Odd 74HCT1G125 behaviour

What is the purpose of easy combat scenarios that don't need resource expenditure?

Slow While Loop, Query Improvment Assistance

How old is the day of 24 equal hours?

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

0

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

share|improve this question

edited Mar 12 '15 at 4:10

Erwin Brandstetter

93.5k9180293

asked Mar 12 '15 at 3:43

boatcoderboatcoder

243210

add a comment | 

0

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

share|improve this question

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

share|improve this question

edited Mar 12 '15 at 4:10

Erwin Brandstetter

93.5k9180293

asked Mar 12 '15 at 3:43

boatcoderboatcoder

243210

"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

create unique index customers_phonetype_customer_id_uniq on customers_phonetype (customer_id, lower(display));

share|improve this question

edited Mar 12 '15 at 4:10

Erwin Brandstetter

93.5k9180293

asked Mar 12 '15 at 3:43

boatcoderboatcoder

243210

share|improve this question

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

asked Mar 12 '15 at 3:43

boatcoderboatcoder

243210

asked Mar 12 '15 at 3:43

boatcoderboatcoder

243210

1 Answer
1

active

oldest

votes

2

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.

share|improve this answer

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
});


}
});

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

2

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.

share|improve this answer

edited 13 mins ago

Pang

1216

answered Mar 12 '15 at 4:08

Erwin BrandstetterErwin Brandstetter

93.5k9180293

add a comment | 

2

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.

share|improve this answer

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.

share|improve this answer

edited 13 mins ago

Pang

1216

answered Mar 12 '15 at 4:08

Erwin BrandstetterErwin Brandstetter

93.5k9180293

ALTER TABLE ADD CONSTRAINT ...

share|improve this answer

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

answered Mar 12 '15 at 4:08

Erwin BrandstetterErwin Brandstetter

93.5k9180293

answered Mar 12 '15 at 4:08

Erwin BrandstetterErwin Brandstetter

93.5k9180293