Postgres Pro
Downloads
Home   >   mailing lists

Thread: fail: alter table NOCHECK CONSTRAINT ALL;

fail: alter table NOCHECK CONSTRAINT ALL;

From
"J.V."
Date:
I have tried:

alter table <table_name> NOCHECK CONSTRAINT ALL;

and it highlights (squiggles) NOCHECK saying : ERROR: syntax error at or
near "NOCHECK" SQL state: 42601 character 20

but everything I lookup says this this is the way to disable all
constraints on a table.

Also is there a way to disable all existing constraints on all tables
everywhere (specifically all pkey & fkey constraints irrespective of how
they were created) in one statement?

I have searched and searched and find nothing that works.


J.V.

Re: fail: alter table NOCHECK CONSTRAINT ALL;

From
Guillaume Lelarge
Date:
On Tue, 2011-10-04 at 12:21 -0600, J.V. wrote:
> I have tried:
>
> alter table <table_name> NOCHECK CONSTRAINT ALL;
>
> and it highlights (squiggles) NOCHECK saying : ERROR: syntax error at or
> near "NOCHECK" SQL state: 42601 character 20
>
> but everything I lookup says this this is the way to disable all
> constraints on a table.
>
> Also is there a way to disable all existing constraints on all tables
> everywhere (specifically all pkey & fkey constraints irrespective of how
> they were created) in one statement?
>
> I have searched and searched and find nothing that works.
>

Not sure where you look up, but there's no way to disable constraints in
PostgreSQL. You can disable triggers and rules, but not the constraints.

See http://www.postgresql.org/docs/9.1/interactive/sql-altertable.html


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: fail: alter table NOCHECK CONSTRAINT ALL;

From
"J.V."
Date:
Is there a way to disable all "trigger user' in one statement? (and then
re-enable?)

One docs says primary keys and foreign keys are "user triggers"

thanks


J.V.


On 10/4/2011 1:12 PM, Guillaume Lelarge wrote:
>
> Not sure where you look up, but there's no way to disable constraints in
> PostgreSQL. You can disable triggers and rules, but not the constraints.
>
> See http://www.postgresql.org/docs/9.1/interactive/sql-altertable.html
>
>

Re: fail: alter table NOCHECK CONSTRAINT ALL;

From
Guillaume Lelarge
Date:
On Tue, 2011-10-04 at 13:20 -0600, J.V. wrote:
> Is there a way to disable all "trigger user' in one statement? (and then
> re-enable?)
>

I guess that if you took the time to read the man page at the URL I gave
you, you would have seen this:

ALTER TABLE [ ONLY ] name [ * ]
    action [, ... ]
...
where action is one of:
...
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]

So, yes, there is a way. For one table, that is.

> One docs says primary keys and foreign keys are "user triggers"
>

Foreign keys are implemented as triggers. If you disable triggers, you
also disable foreign keys. It doesn't apply to primary keys, which
aren't triggers.

And, please, stop top-posting.


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com