Thread: [HACKERS] alter table..drop constraint pkey, left not null un-dropped
[HACKERS] alter table..drop constraint pkey, left not null un-dropped
From
Rajkumar Raghuwanshi
Date:
Hi All,
I have created a table with primary key, and then dropped primary key from table. But table still have not null constraint added by primary key.
Is there any other statement to delete primary key with not null?
or this is an expected behaviour of pg?
postgres=# create table tbl (c1 int primary key);
CREATE TABLE
postgres=# \d+ tbl
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------
c1 | integer | | not null | | plain | |
Indexes:
"tbl_pkey" PRIMARY KEY, btree (c1)
postgres=# alter table tbl drop constraint tbl_pkey;
ALTER TABLE
postgres=# \d+ tbl
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------
c1 | integer | | not null | | plain | |
I have created a table with primary key, and then dropped primary key from table. But table still have not null constraint added by primary key.
Is there any other statement to delete primary key with not null?
or this is an expected behaviour of pg?
postgres=# create table tbl (c1 int primary key);
CREATE TABLE
postgres=# \d+ tbl
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------
c1 | integer | | not null | | plain | |
Indexes:
"tbl_pkey" PRIMARY KEY, btree (c1)
postgres=# alter table tbl drop constraint tbl_pkey;
ALTER TABLE
postgres=# \d+ tbl
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------
c1 | integer | | not null | | plain | |
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
On Thu, May 11, 2017 at 3:03 PM, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> wrote: > Hi All, > > I have created a table with primary key, and then dropped primary key from > table. But table still have not null constraint added by primary key. > > Is there any other statement to delete primary key with not null? > or this is an expected behaviour of pg? > > postgres=# create table tbl (c1 int primary key); > CREATE TABLE > postgres=# \d+ tbl > Table "public.tbl" > Column | Type | Collation | Nullable | Default | Storage | Stats target > | Description > --------+---------+-----------+----------+---------+---------+--------------+------------- > c1 | integer | | not null | | plain | > | > Indexes: > "tbl_pkey" PRIMARY KEY, btree (c1) > > postgres=# alter table tbl drop constraint tbl_pkey; > ALTER TABLE > postgres=# \d+ tbl > Table "public.tbl" > Column | Type | Collation | Nullable | Default | Storage | Stats target > | Description > --------+---------+-----------+----------+---------+---------+--------------+------------- > c1 | integer | | not null | | plain | > | I don't think we have a way to tell whether NOT NULL constraint was added for primary key or independently. So, I guess, we can not just drop it while dropping primary key constraint. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> writes:
> I have created a table with primary key, and then dropped primary key from
> table. But table still have not null constraint added by primary key.
> Is there any other statement to delete primary key with not null?
> or this is an expected behaviour of pg?
Yes, it's expected. You can use "alter table t alter column c drop not
null" if you don't want the not-null constraint anymore.
regards, tom lane