Re: alter table..drop constraint pkey, left not null un-dropped

On Thu, May 11, 2017 at 3:03 PM, Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)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