HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Advantages to column constraints over table constraints

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
constraintscolumnadvantagesovertable

Problem

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?

Solution

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)
);

Code Snippets

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

Context

StackExchange Database Administrators Q#209708, answer score: 5

Revisions (0)

No revisions yet.