Re: [HACKERS] Foreign keys: unexpected result from ALTER TABLE... ADD CONSTRAINT...

On Mon, 17 Jan 2000, Oliver Elphick wrote:

> I cannot do this pair of table creations directly, because they are mutually
> dependent.
> 

I don't think this will ever work. I can't really decode your intentions
here but I recall that translating proper relational schemas (you know,
the ones with the bubbles and lines) into tables never creates this sort
of situation. Then again I could be wrong.

> 
> create table purchased_job
> (
>     supplier    char(10)    not null
>                         references supplier (id) match full,
>     specification    text,
>     del_point    char(2)        not null
>                         references location (id) match full,
>     import_licence    bool        default 'f',
>     import_duty    numeric(12,2),
>     terms        char(3),
>     deliv_clear    numeric(12,2),
> 
>     foreign key (product, supplier) references product_supplier (product, 
> supplier) match full
> )
>     inherits (job)
> ;
> 
> 
> 
> create table product_supplier
> (
>     product        char(10)        not null
>                         references purchased_job (product) match full,
>     supplier    char(10)    not null
>                         references supplier (id) match full,
> 
>     primary key (product, supplier)
> )
> ;
> 
> so I omitted the foreign key specification from the creation of purchased_job
> and tried to add it afterwards, but (after fixing a bug in gram.y) I found
> that ALTER TABLE ... ADD CONSTRAINT is not yet implemented.  Is there, then, any
> way to create this mutual dependency?

Thanks for that fix, that was me changing the grammar for an ALTER TABLE /
ALTER COLUMN implementation, which now works btw.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden