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
|
# name: test/sql/attach/attach_foreign_key.test
# description: Test attach mixed with foreign key constraints
# group: [attach]
statement ok
ATTACH DATABASE ':memory:' AS db1;
statement ok
CREATE TABLE album(artistid INTEGER, albumname TEXT, albumcover TEXT, UNIQUE (artistid, albumname));
statement error
CREATE TABLE db1.song(songid INTEGER, songartist INTEGER, songalbum TEXT, songname TEXT, FOREIGN KEY(songartist, songalbum) REFERENCES album(artistid, albumname));
----
across different schemas or catalogs
statement ok
USE db1;
statement ok
CREATE TABLE album(artistid INTEGER, albumname TEXT, albumcover TEXT, UNIQUE (artistid, albumname));
statement ok
INSERT INTO album VALUES (1, 'A', 'A_cover'), (2, 'B', 'B_cover'), (3, 'C', 'C_cover'), (4, 'D', 'D_cover');
statement ok
CREATE TABLE song(songid INTEGER, songartist INTEGER, songalbum TEXT, songname TEXT, FOREIGN KEY(songartist, songalbum) REFERENCES album(artistid, albumname));
statement ok
ATTACH DATABASE ':memory:' AS db2;
statement ok
USE db2;
# check that foreign key is correctly resolved even when different catalog search path is used
statement ok
INSERT INTO db1.song VALUES (11, 1, 'A', 'A_song'), (12, 2, 'B', 'B_song'), (13, 3, 'C', 'C_song');
|
