Thread: ALTER TABLE ADD CONSTRAINT

I am generating scripts from MSSQL Server and converting them to create objects in PostgreSQL.  It is suprisingly easy.
However, I think I may have hit a rock. 

It appears that PostgreSQL does not support listing constraints to be added as in the following syntax:

alter table foo add
    constraint fk_foo_bar foreign key
    (
        fooid
    ) references bar (
        barid
    ),
    constraint fk_foo_baz foreign key
    (
        footooid
    ) references baz (
        bazid
    );

Is this true?  Is this something that is being considered for addition?

Also, I see that alter table add constraint does not work for defaults.  Is this something that is going to be added?

Thanks!!

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: ianh@tpchd.org

> I am generating scripts from MSSQL Server and converting them to create
objects in PostgreSQL.  It is suprisingly easy.  However, I think I may have
hit a rock.
>
> It appears that PostgreSQL does not support listing constraints to be
added as in the following syntax:

I don't know why it won't accept that syntax, but I suspect that it might be
choking on the multiple additional constraints. Try adding each constraint
as a separate ALTER TABLE statement. This should work:

ALTER TABLE foo ADD FOREIGN KEY (fooid) REFERENCES bar (barid);
ALTER TABLE foo ADD FOREIGN KEY (footooid) REFERENCES baz (bazid);

> Is this true?  Is this something that is being considered for addition?

If you can add multiple constraints via a single ALTER TABLE according to
the SQL standard, I suspect that it will be added someday. But since you can
add them individually, I would suspect the priority would be VERY low on the
list.

> Also, I see that alter table add constraint does not work for defaults.
Is this something that is going to be added?

That I do hope will be added since the only way to replicate the
functionality is to drop, readd and repopulate a table.

Greg

>> Also, I see that alter table add constraint does not work for defaults.
>>
>> Is this something that is going to be added?
>
> That I do hope will be added since the only way to replicate the
> functionality is to drop, readd and repopulate a table.
>
> Greg

I am not sure specifically what you are looking for but you can add a
default value to a column in an ALTER TABLE statement:

ALTER TABLE two ALTER COLUMN col_b SET DEFAULT 1;

Beyond that you can use another ALTER statement to add a Foreign Key
constraint to the column:

ALTER TABLE two ADD CONSTRAINT two_fk foreign key(col_b) REFERENCES
tbl_one(col_a) match full;

The biggest thing that I have noticed is many things require you to use
separate ALTER statements in order for them to work.

I hope this helps.

jeff


Jeff Daugherty
Database Systems Engineer
Great Bridge, LLC

On Mon, 7 May 2001, Ian Harding wrote:

> I am generating scripts from MSSQL Server and converting them to
> create objects in PostgreSQL.  It is suprisingly easy.  However, I
> think I may have hit a rock.
>
> It appears that PostgreSQL does not support listing constraints to be
> added as in the following syntax:
>
> alter table foo add
>     constraint fk_foo_bar foreign key
>     (
>         fooid
>     ) references bar (
>         barid
>     ),
>     constraint fk_foo_baz foreign key
>     (
>         footooid
>     ) references baz (
>         bazid
>     );
>
> Is this true?  Is this something that is being considered for addition?

AFAICS, that's not supported by the grammar in the SQL spec, however
it may be a reasonable extension.

> Also, I see that alter table add constraint does not work for
> defaults.  Is this something that is going to be added?
again AFAICS, add constraint has nothing to do with defaults,
it's for adding, unique/pkey, fkey and check constraints.  Ours
currently doesn't handle unique/pkey.  Probably will for 7.2.

> >> Also, I see that alter table add constraint does not work for defaults.
> >>
> >> Is this something that is going to be added?
> >
> > That I do hope will be added since the only way to replicate the
> > functionality is to drop, readd and repopulate a table.
> >
> > Greg
>
> I am not sure specifically what you are looking for but you can add a
> default value to a column in an ALTER TABLE statement:
>
> ALTER TABLE two ALTER COLUMN col_b SET DEFAULT 1;

Sorry, I was assuming the original author was correct in his statement. I
thought there was some limitation here though... maybe it was dropping the
default that I was thinking of.

> The biggest thing that I have noticed is many things require you to use
> separate ALTER statements in order for them to work.

That's what I was pointing out :)

Greg