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:

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