From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Forward declaration of table |
Date: | 2016-08-23 17:52:11 |
Message-ID: | ab5ebb79-852c-1707-fae4-0a5eba21029a@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 08/23/2016 10:29 AM, David G. Johnston wrote:
> On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>wrote:
>
>
> use ALTER TABLE ADD table_constraint :
>
> https://www.postgresql.org/docs/9.5/static/sql-altertable.html
> <https://www.postgresql.org/docs/9.5/static/sql-altertable.html>
>
> to add the FK references to word_games.
>
>
> Hadn't considered "ALTER TABLE" but I'd be afraid of dump-restore
> hazards here. Maybe pg_dump is smart enough to handle this correctly,
> though - maybe by adding constraint definitions after all tables and
> columns are present.
It does. Though the usual caveats about doing partial dumps apply, eg if
I had only specified -t fk_child below I would not get fk_parent
automatically:
postgres(at)test=# create table fk_child(id int, fk_id int);
CREATE TABLE
postgres(at)test=# create table fk_parent(id int, some_id int UNIQUE);
CREATE TABLE
postgres(at)test=# alter table fk_child ADD CONSTRAINT fk_constraint
FOREIGN KEY (fk_id) REFERENCES fk_parent(some_id);
ALTER TABLE
pg_dump -d test -U postgres -t fk_parent -t fk_child -f test.sql
--
-- Name: fk_child; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE fk_child (
id integer,
fk_id integer
);
ALTER TABLE fk_child OWNER TO postgres;
--
-- Name: fk_parent; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE fk_parent (
id integer,
some_id integer
);
ALTER TABLE fk_parent OWNER TO postgres;
--
-- Data for Name: fk_child; Type: TABLE DATA; Schema: public; Owner:
postgres
--
COPY fk_child (id, fk_id) FROM stdin;
\.
--
-- Data for Name: fk_parent; Type: TABLE DATA; Schema: public; Owner:
postgres
--
COPY fk_parent (id, some_id) FROM stdin;
\.
--
-- Name: fk_parent_some_id_key; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY fk_parent
ADD CONSTRAINT fk_parent_some_id_key UNIQUE (some_id);
--
-- Name: fk_constraint; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY fk_child
ADD CONSTRAINT fk_constraint FOREIGN KEY (fk_id) REFERENCES
fk_parent(some_id);
>
> David J.
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2016-08-23 18:15:29 | Re: Forward declaration of table |
Previous Message | Periko Support | 2016-08-23 17:35:02 | Pentaho Odoo PSQL Slave. |