Thread: How can I quit this: "Unique " to my table?
Hello pgsql-sql, When my table was create I wrote this: MyDatabase=# create table b_prestamo(no_inventa char(16) unique not null, cve_area char(10), date1 date); I need now thatmy table can accept repeated records, but not Null. How can I do that? SomeBody can help me please? My table has informationat the moment,what can I do for maintain it? -- Best regards,Luis mailto:ksql2002@yahoo.com.mx
On Tue, 1 Apr 2003, Luis Mix wrote: > Hello pgsql-sql, > > When my table was create I wrote this: > > MyDatabase=# create table b_prestamo(no_inventa char(16) unique not > null, cve_area char(10), date1 date); > I need now that my table can accept repeated records, but not Null. > How can I do that? > SomeBody can help me please? > My table has information at the moment,what can I do for maintain > it? drop index b_prestamo_no_inventa_idx; > > > > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
On Tue, 1 Apr 2003, Luis Mix wrote: > Hello pgsql-sql, > > When my table was create I wrote this: > > MyDatabase=# create table b_prestamo(no_inventa char(16) unique not > null, cve_area char(10), date1 date); > I need now that my table can accept repeated records, but not Null. > How can I do that? > SomeBody can help me please? > My table has information at the moment,what can I do for maintain > it? Sorry man :( the correct statement is ALTER TABLE b_prestamo drop constraint b_prestamo_no_inventa_key; > > > > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
On Tuesday 01 April 2003 21:26, Achilleus Mantzios wrote: > On Tue, 1 Apr 2003, Luis Mix wrote: > > Hello pgsql-sql, > > > > When my table was create I wrote this: > > > > MyDatabase=# create table b_prestamo(no_inventa char(16) unique not > > null, cve_area char(10), date1 date); > > I need now that my table can accept repeated records, but not Null. > > How can I do that? > > SomeBody can help me please? > > My table has information at the moment,what can I do for maintain > > it? > > drop index b_prestamo_no_inventa_idx; ALTER TABLE b_prestamo DROP CONSTRAINT b_prestamo_no_inventa_key ?? Ian Barwick barwick@gmx.net
Ian Barwick <barwick@gmx.net> writes:
> On Tuesday 01 April 2003 21:26, Achilleus Mantzios wrote:
>> drop index b_prestamo_no_inventa_idx;
> ALTER TABLE b_prestamo DROP CONSTRAINT b_prestamo_no_inventa_key ??
The correct answer is "one or the other depending on which PG version
he's running" ;-). Before 7.3, drop the index; as of 7.3 you're
supposed to drop the constraint instead. (No harm in trying the wrong
one, it just won't let you do it.)
Looking at the output of psql's "\d b_prestamo" would be a good idea to
verify the spelling of the index and/or constraint name.
regards, tom lane
Hi Luis, I presume you want to remove the unique column constraint? The ALTER TABLE command doesn't allow you to do this so another way is as follows: pg_dump -t b_prestamo MyDatabase > b_prestamo.sql Edit the b_prestamo.sql file:Add DROP TABLE b_prestamo; before the create table statement.Remove the unique keyword fromthe no_inventa column psql MyDatabase < b_prestamo.sql This will drop and recreate the table and reload the data currently in the table (the pg_dump file contains the schema andthe data for the table). If you have any reference or foreign key constraints that refer to the b_prestamo table, then you will need to drop thoseconstraints and add them back after changing the table. Regards, David Witham Telephony Platforms Architect Unidial -----Original Message----- From: Luis Mix [mailto:ksql2002@yahoo.com.mx] Sent: Wednesday, 2 April 2003 00:35 To: pgsql-sql@postgresql.org Subject: [SQL] How can I quit this: "Unique " to my table? Hello pgsql-sql, When my table was create I wrote this: MyDatabase=# create table b_prestamo(no_inventa char(16) unique not null, cve_area char(10), date1 date); I need now thatmy table can accept repeated records, but not Null. How can I do that? SomeBody can help me please? My table has informationat the moment,what can I do for maintain it? -- Best regards,Luis mailto:ksql2002@yahoo.com.mx ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
If the constraint were named then it could be dropped - another good reason to name all constraints. I like Oracle Designer's way of applying the constraints with ALTER TABLE rather than CREATE TABLE - maintenance is much easier. > I presume you want to remove the unique column constraint? > > The ALTER TABLE command doesn't allow you to do this so another way > is as follows: ... > > If you have any reference or foreign key constraints that refer to > the b_prestamo table, then you will need to drop those constraints > and add them back after changing the table. > > Regards, > David Witham > Telephony Platforms Architect > Unidial > > -----Original Message----- > From: Luis Mix [mailto:ksql2002@yahoo.com.mx] > Sent: Wednesday, 2 April 2003 00:35 > To: pgsql-sql@postgresql.org > Subject: [SQL] How can I quit this: "Unique " to my table? > > > Hello pgsql-sql, > > When my table was create I wrote this: > > MyDatabase=# create table b_prestamo(no_inventa char(16) unique not > null, cve_area char(10), date1 date); > I need now that my table can accept repeated records, but not Null. > How can I do that? > SomeBody can help me please? > My table has information at the moment,what can I do for maintain > it? > > > > -- > Best regards, > Luis mailto:ksql2002@yahoo.com.mx > __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://platinum.yahoo.com