Thread: 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