Thread: drop a check
Dear all... I have created a check constraint without giving it a name.. now I have a check named "$25" in my table that I need to drop or modify!!! How can I do??? with names is simple alter table tablename drop constraint constraintname; but without name?? :-) many thanks... Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: marchesini@unipg.it ivan.marchesini@gmail.com tel: +39(0)755853760 fax: +39(0)755853756 jabber: geoivan73@jabber.org
On Nov 13 05:32, ivan marchesini wrote: > I have created a check constraint without giving it a name.. > now I have a check named "$25" in my table that I need to drop or > modify!!! > How can I do??? > with names is simple > > alter table tablename drop constraint constraintname; > > but without name?? :-) CREATE TABLE cons_test ( u integer CHECK (u > 10) ); SELECT constraint_name FROM information_schema.constraint_column_usageWHERE table_name = 'cons_test' AND column_name= 'u'; See infoschema-constraint-column-usage.html in the manual for further assistance. Regards.
ivan marchesini <marchesini@unipg.it> schrieb: > Dear all... > I have created a check constraint without giving it a name.. > now I have a check named "$25" in my table that I need to drop or > modify!!! > How can I do??? Can you see the name with \d <table> within psql? An example: test=# create table bla (id int check (id between 1 and 4)); CREATE TABLE test=# \d bla Table "public.bla"Column | Type | Modifiers --------+---------+-----------id | integer | Check constraints: "bla_id_check" CHECK (id >= 1 AND id <= 4) test=# alter table bla drop CONSTRAINT bla_id_check; ALTER TABLE test=# \d bla Table "public.bla"Column | Type | Modifiers --------+---------+-----------id | integer | HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Dear Volkan, thank you for your answer... I have tried to verify the table information_schema.constraint_column_usage, but, and I was really surprised, I have found nothing inside it.. it seems there are not checks!!! I have also seen the manual page you suggested but I can't understand why I don't have checks in this table... but I'm sure I have checks in my table.. because they works!!!!! does this problem can be related to the fact that I have created the checks only after that I have created the table... I have used this syntacs: alter table tablename add check (......) however it sounds strange!!!!! thanks!!! Ivan On lun, 2006-11-13 at 19:41 +0200, Volkan YAZICI wrote: > On Nov 13 05:32, ivan marchesini wrote: > > I have created a check constraint without giving it a name.. > > now I have a check named "$25" in my table that I need to drop or > > modify!!! > > How can I do??? > > with names is simple > > > > alter table tablename drop constraint constraintname; > > > > but without name?? :-) > > CREATE TABLE cons_test ( > u integer CHECK (u > 10) > ); > > SELECT constraint_name > FROM information_schema.constraint_column_usage > WHERE table_name = 'cons_test' AND > column_name = 'u'; > > See infoschema-constraint-column-usage.html in the manual for further > assistance. > > > Regards. > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: marchesini@unipg.it ivan.marchesini@gmail.com tel: +39(0)755853760 fax: +39(0)755853756 jabber: geoivan73@jabber.org
..... or it depend on the fact I'm using postgres 7.4.13 many thanks... Ivan On lun, 2006-11-13 at 19:41 +0200, Volkan YAZICI wrote: > On Nov 13 05:32, ivan marchesini wrote: > > I have created a check constraint without giving it a name.. > > now I have a check named "$25" in my table that I need to drop or > > modify!!! > > How can I do??? > > with names is simple > > > > alter table tablename drop constraint constraintname; > > > > but without name?? :-) > > CREATE TABLE cons_test ( > u integer CHECK (u > 10) > ); > > SELECT constraint_name > FROM information_schema.constraint_column_usage > WHERE table_name = 'cons_test' AND > column_name = 'u'; > > See infoschema-constraint-column-usage.html in the manual for further > assistance. > > > Regards. > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: marchesini@unipg.it ivan.marchesini@gmail.com tel: +39(0)755853760 fax: +39(0)755853756 jabber: geoivan73@jabber.org
On Nov 13 06:38, ivan marchesini wrote: > thank you for your answer... > I have tried to verify the table > information_schema.constraint_column_usage, but, and I was really > surprised, I have found nothing inside it.. > it seems there are not checks!!! > I have also seen the manual page you suggested but I can't understand > why I don't have checks in this table... > > but I'm sure I have checks in my table.. because they works!!!!! > does this problem can be related to the fact that I have created the > checks only after that I have created the table... > I have used this syntacs: > alter table tablename add check (......) I tried to produce same strange behaviour with no luck: test=# CREATE TABLE cons_test (u int); CREATE TABLE test=# ALTER TABLE cons_test test-# ADD CONSTRAINT forget_my_name CHECK (u > 10); ALTER TABLE test=# SELECT constraint_name test-# FROM information_schema.constraint_column_usage test-# WHERE table_name = 'cons_test' AND test-# column_name = 'u';constraint_name -----------------forget_my_name (1 row) Also, you cannot see that constraint listed in the \d table_name output, ain't? Maybe you should try a hardcoded search over consrc column of pg_catalog.pg_constraint table. Regards.
ivan marchesini <marchesini@unipg.it> schrieb: > ..... > or it depend on the fact I'm using postgres 7.4.13 > many thanks... Maybe, the information-schema can be different in different versions. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°