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
53
54
55
56
57
58
|
# name: test/sql/table_function/duckdb_constraints.test
# description: Test duckdb_constraints function
# group: [table_function]
statement ok
create table integers(i int primary key, check (i < 10));
statement ok
create table test(i varchar unique, k varchar, check(len(i || k) < 10));
statement ok
create table fk_integers(j int, foreign key (j) references integers(i));
statement ok
create table fk_integers_2(k int, foreign key (k) references integers(i));
statement ok nosort duckdb_col
SELECT * FROM duckdb_constraints();
statement ok nosort duckdb_col
SELECT * FROM duckdb_constraints;
query IIII
SELECT table_name, constraint_index, constraint_type, UNNEST(constraint_column_names) col_name FROM duckdb_constraints ORDER BY table_name, constraint_index, col_name
----
fk_integers 0 FOREIGN KEY j
fk_integers_2 1 FOREIGN KEY k
integers 2 PRIMARY KEY i
integers 3 CHECK i
integers 4 NOT NULL i
test 5 UNIQUE i
test 6 CHECK i
test 6 CHECK k
query II
SELECT constraint_name, unique_constraint_name FROM information_schema.referential_constraints ORDER BY constraint_name
----
fk_integers_2_k_i_fkey integers_i_pkey
fk_integers_j_i_fkey integers_i_pkey
query IIII
SELECT column_name, constraint_name, table_name, position_in_unique_constraint FROM information_schema.key_column_usage ORDER BY constraint_name
----
k fk_integers_2_k_i_fkey fk_integers_2 1
j fk_integers_j_i_fkey fk_integers 1
i integers_i_pkey integers NULL
i test_i_key test NULL
query III
SELECT constraint_name, table_name, constraint_type FROM information_schema.table_constraints ORDER BY constraint_name;
----
fk_integers_2_k_i_fkey fk_integers_2 FOREIGN KEY
fk_integers_j_i_fkey fk_integers FOREIGN KEY
integers_i_check integers CHECK
integers_i_not_null integers CHECK
integers_i_pkey integers PRIMARY KEY
test_i_k_check test CHECK
test_i_key test UNIQUE
|
