4

Is there any advantages whatsoever, other than style to writing this,

CREATE TABLE foo (
  a int PRIMARY KEY,
  b int
);

And then,

CREATE TABLE bar (
  a int REFERENCES foo,
  c int
);

Over,

BEGIN;
  CREATE TABLE bar (
    a int,
    c int
  );
  ALTER TABLE bar
    ADD FOREIGN KEY (a)
    REFERENCES foo;
COMMIT;

I'm trying to build a DDL generator, so I'm wondering if pays to keep constraints on the column (where I was generating them before), or to move them all outside to the table? I know these results produce the same table, I'm just wondering if there is any advantage under the hood -- less wal? etc?

5

There's no difference both DDLs result in the same database structure.

The second form (or the following form) is required when the FOREIGN KEY relationship uses a compound key.

CREATE TABLE bar (
    a int,
    c int,
    FOREIGN KEY (a,c) REFERENCES foo(a,c)
);
1

From my perspective:

CREATE TABLE bar (
  a int REFERENCES foo,
  c int
);

is easier to understand than:

BEGIN;
  CREATE TABLE bar (
    a int,
    c int
  );
  ALTER TABLE bar
    ADD FOREIGN KEY (a)
    REFERENCES foo;
COMMIT;

On the other hand, it's easier to parse the latter, in case you would like to loop over your table definitions and first create tables, then constraints (without having to sort the tables in topological order).

FWIW, I use the second form for all but generated columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.