patternsqlMinor
Advantages to column constraints over table constraints
Viewed 0 times
constraintscolumnadvantagesovertable
Problem
Is there any advantages whatsoever, other than style to writing this,
And then,
Over,
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?
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
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.