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

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