1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
# name: test/sql/table_function/duckdb_constraints_fk.test
# description: Test duckdb_constraints function
# group: [table_function]
statement ok
CREATE TABLE tf_1 (
a integer, "b c" integer, "d e" integer,
PRIMARY KEY (a),
UNIQUE ("b c"),
UNIQUE ("d e")
);
statement ok
CREATE TABLE tf_3 (
g integer, h integer,
PRIMARY KEY (g),
UNIQUE (h)
);
statement ok
CREATE TABLE tf_2 (
c integer, d integer, e integer, f integer, g integer,
PRIMARY KEY (c),
FOREIGN KEY (d) REFERENCES tf_1 (a),
FOREIGN KEY (e) REFERENCES tf_1 ("b c"),
FOREIGN KEY (f) REFERENCES tf_1 ("d e"),
FOREIGN KEY (g) REFERENCES tf_3 (g),
);
statement ok
CREATE TABLE tf_4 (
h integer,
FOREIGN KEY (h) REFERENCES tf_3 (h),
);
query IIIIIIIIII
SELECT * EXCLUDE (database_name, schema_oid, table_oid, database_oid, constraint_name) FROM duckdb_constraints();
----
main tf_1 0 PRIMARY KEY PRIMARY KEY(a) NULL [0] [a] NULL []
main tf_1 1 UNIQUE UNIQUE("b c") NULL [1] [b c] NULL []
main tf_1 2 UNIQUE UNIQUE("d e") NULL [2] [d e] NULL []
main tf_1 3 NOT NULL NOT NULL NULL [0] [a] NULL []
main tf_2 4 PRIMARY KEY PRIMARY KEY(c) NULL [0] [c] NULL []
main tf_2 5 FOREIGN KEY FOREIGN KEY (d) REFERENCES tf_1(a) NULL [1] [d] tf_1 [a]
main tf_2 6 FOREIGN KEY FOREIGN KEY (e) REFERENCES tf_1("b c") NULL [2] [e] tf_1 [b c]
main tf_2 7 FOREIGN KEY FOREIGN KEY (f) REFERENCES tf_1("d e") NULL [3] [f] tf_1 [d e]
main tf_2 8 FOREIGN KEY FOREIGN KEY (g) REFERENCES tf_3(g) NULL [4] [g] tf_3 [g]
main tf_2 9 NOT NULL NOT NULL NULL [0] [c] NULL []
main tf_3 10 PRIMARY KEY PRIMARY KEY(g) NULL [0] [g] NULL []
main tf_3 11 UNIQUE UNIQUE(h) NULL [1] [h] NULL []
main tf_3 12 NOT NULL NOT NULL NULL [0] [g] NULL []
main tf_4 13 FOREIGN KEY FOREIGN KEY (h) REFERENCES tf_3(h) NULL [0] [h] tf_3 [h]
|
