Thread: ALTER TABLE .... make constraint DEFERRABLE
Deferrable unique constraints seem an interesting feature, though I have either some questions or some issues, not sure which. I don't seem to be able to find any way to do an ALTER TABLE that adds this new capability to an existing table. There is no way to add a constraint via a CREATE TABLE AS SELECT, so that means there is no way to use the feature at all in that case. Also, foreign keys can't be defined that refer to a deferrable primary key. That isn't mentioned at all in the manual with regard to the DEFERRABLE clause, though it is mentioned in the FK section. You get this error message ERROR: cannot use a deferrable unique constraint for referenced table The use case for this feature looks a little narrow at present. Can we do something about usability? Am I missing something? -- Simon Riggs www.2ndQuadrant.com
Simon Riggs <simon@2ndQuadrant.com> writes:
> Am I missing something?
That we're long past feature freeze? Some of these might be reasonable
TODO items. None of them are happening for 9.0.
regards, tom lane
Simon Riggs wrote: > > Deferrable unique constraints seem an interesting feature, though I have > either some questions or some issues, not sure which. > > I don't seem to be able to find any way to do an ALTER TABLE that adds > this new capability to an existing table. I was able to do it: test=> create table test (x int unique DEFERRABLE INITIALLY DEFERRED);NOTICE: CREATE TABLE / UNIQUE will create implicitindex "test_x_key"for table "test"CREATE TABLE test=> alter table test add column y int;ALTER TABLE test=> alter table test add unique (y) DEFERRABLE INITIALLY DEFERRED;NOTICE: ALTER TABLE / ADD UNIQUE will create implicitindex"test_y_key" for table "test"ALTER TABLE Is that what you were asking? > There is no way to add a constraint via a CREATE TABLE AS SELECT, so > that means there is no way to use the feature at all in that case. Uh, CREATE TABLE AS SELECT seems to be very limited, but I have not heard any complaints about it before. > Also, foreign keys can't be defined that refer to a deferrable primary > key. That isn't mentioned at all in the manual with regard to the > DEFERRABLE clause, though it is mentioned in the FK section. You get > this error message > ERROR: cannot use a deferrable unique constraint for referenced table > > The use case for this feature looks a little narrow at present. Can we > do something about usability? Not sure why that was a limitation. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
On 3 June 2010 02:06, Bruce Momjian <bruce@momjian.us> wrote: >> Also, foreign keys can't be defined that refer to a deferrable primary >> key. That isn't mentioned at all in the manual with regard to the >> DEFERRABLE clause, though it is mentioned in the FK section. You get >> this error message >> ERROR: cannot use a deferrable unique constraint for referenced table >> >> The use case for this feature looks a little narrow at present. Can we >> do something about usability? > > Not sure why that was a limitation. > That's in accordance with the SQL spec. I didn't think of this case originally, but all sorts of complications would arise if we were to allow FKs to refer to deferrable PKs. For example, if there are 2 temporarily duplicated PKs, and you update one of them, what would the FK's ON UPDATE actions do? I'm not convinced there is any sensible answer to this question. Regards, Dean
On Wed, 2010-06-02 at 21:06 -0400, Bruce Momjian wrote: > Simon Riggs wrote: > > > > Deferrable unique constraints seem an interesting feature, though I have > > either some questions or some issues, not sure which. > > > > I don't seem to be able to find any way to do an ALTER TABLE that adds > > this new capability to an existing table. > > I was able to do it: > > test=> create table test (x int unique DEFERRABLE INITIALLY DEFERRED); > NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_x_key" > for table "test" > CREATE TABLE > > test=> alter table test add column y int; > ALTER TABLE > > test=> alter table test add unique (y) DEFERRABLE INITIALLY DEFERRED; > NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index > "test_y_key" for table "test" > ALTER TABLE > > Is that what you were asking? No. I wanted to defer an existing UNIQUE constraint. That doesn't seem to be possible. You *can* add a whole new constraint and then drop the old one, though that's not quite as cool. > > There is no way to add a constraint via a CREATE TABLE AS SELECT, so > > that means there is no way to use the feature at all in that case. > > Uh, CREATE TABLE AS SELECT seems to be very limited, but I have not > heard any complaints about it before. > > > Also, foreign keys can't be defined that refer to a deferrable primary > > key. That isn't mentioned at all in the manual with regard to the > > DEFERRABLE clause, though it is mentioned in the FK section. You get > > this error message > > ERROR: cannot use a deferrable unique constraint for referenced table > > > > The use case for this feature looks a little narrow at present. Can we > > do something about usability? > > Not sure why that was a limitation. Regrettably it makes it an unusable limitation for many people. All large tables are referenced in a typical database that uses PKs/FKs. -- Simon Riggs www.2ndQuadrant.com
Simon Riggs wrote: > On Wed, 2010-06-02 at 21:06 -0400, Bruce Momjian wrote: > > Simon Riggs wrote: > > > > > > Deferrable unique constraints seem an interesting feature, though I have > > > either some questions or some issues, not sure which. > > > > > > I don't seem to be able to find any way to do an ALTER TABLE that adds > > > this new capability to an existing table. > > > > I was able to do it: > > > > test=> create table test (x int unique DEFERRABLE INITIALLY DEFERRED); > > NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_x_key" > > for table "test" > > CREATE TABLE > > > > test=> alter table test add column y int; > > ALTER TABLE > > > > test=> alter table test add unique (y) DEFERRABLE INITIALLY DEFERRED; > > NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index > > "test_y_key" for table "test" > > ALTER TABLE > > > > Is that what you were asking? > > No. I wanted to defer an existing UNIQUE constraint. That doesn't seem > to be possible. You *can* add a whole new constraint and then drop the > old one, though that's not quite as cool. Do we allow any kind of constraint modification via ALTER TABLE? I don't see much listed in the manual. > > > There is no way to add a constraint via a CREATE TABLE AS SELECT, so > > > that means there is no way to use the feature at all in that case. > > > > Uh, CREATE TABLE AS SELECT seems to be very limited, but I have not > > heard any complaints about it before. > > > > > Also, foreign keys can't be defined that refer to a deferrable primary > > > key. That isn't mentioned at all in the manual with regard to the > > > DEFERRABLE clause, though it is mentioned in the FK section. You get > > > this error message > > > ERROR: cannot use a deferrable unique constraint for referenced table > > > > > > The use case for this feature looks a little narrow at present. Can we > > > do something about usability? > > > > Not sure why that was a limitation. > > Regrettably it makes it an unusable limitation for many people. > > All large tables are referenced in a typical database that uses PKs/FKs. Yeah, no question. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +