Обсуждение: Drop column constraint

A table has a unique constraint on a column that needs removing. Reading the
postgres-12.x docs for alter table it appears the correct syntax is:

alter table locations drop constraint unique;

but this is wrong.

Trying 'alter table locations alter column loc_nbr drop constraint unique;' also
failed.

What's the proper syntax to drop the unique constraint on a table column?

TIA,

Rich

On 10/30/20 8:30 AM, Rich Shepard wrote:
> A table has a unique constraint on a column that needs removing. Reading 
> the
> postgres-12.x docs for alter table it appears the correct syntax is:
> 
> alter table locations drop constraint unique;

It should be:

alter table locations drop constraint 'constraint_name';

Where you can find 'constraint_name' from:

\d locations

> 
> but this is wrong.
> 
> Trying 'alter table locations alter column loc_nbr drop constraint 
> unique;' also
> failed.
> 
> What's the proper syntax to drop the unique constraint on a table column?
> 
> TIA,
> 
> Rich
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 30/10/2020 15:30, Rich Shepard wrote:
> A table has a unique constraint on a column that needs removing. 
> Reading the
> postgres-12.x docs for alter table it appears the correct syntax is:
>
> alter table locations drop constraint unique;
>
> but this is wrong.
>
> Trying 'alter table locations alter column loc_nbr drop constraint 
> unique;' also
> failed.
>
> What's the proper syntax to drop the unique constraint on a table column?
>
> TIA,
>
> Rich
>
>
>
You need

alter table locations drop constraint <constraint name>;


Cheers, Chris Sterritt

On Fri, 30 Oct 2020, Adrian Klaver wrote:

> It should be:
> alter table locations drop constraint 'constraint_name';

Adrian,

Yes, I forgot to quote the constraint_name, And, I used the DDL name
'unique' rather than the internal name "locations_loc_nbr_key". Using the
latter, and adding 'cascade' (because the dependent table is empty) did the
trick.

Thank you,

Rich

On 10/30/20 8:54 AM, Rich Shepard wrote:
> On Fri, 30 Oct 2020, Adrian Klaver wrote:
> 
>> It should be:
>> alter table locations drop constraint 'constraint_name';
> 
> Adrian,
> 
> Yes, I forgot to quote the constraint_name, And, I used the DDL name
> 'unique' rather than the internal name "locations_loc_nbr_key". Using the

Actually unique is not the name, it is the constraint type. You can 
create your own name when creating the constraint or Postgres will 
create one for you.

> latter, and adding 'cascade' (because the dependent table is empty) did the
> trick.
> 
> Thank you,
> 
> Rich
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 30 Oct 2020, Adrian Klaver wrote:

> Actually unique is not the name, it is the constraint type. You can create
> your own name when creating the constraint or Postgres will create one for
> you.

Adrian,

Got it, thanks.

Rich