github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/dependencies (about) 1 statement ok 2 CREATE TABLE test_kv(k INT PRIMARY KEY, v INT, w DECIMAL); 3 CREATE UNIQUE INDEX test_v_idx ON test_kv(v); 4 CREATE INDEX test_v_idx2 ON test_kv(v DESC) STORING(w); 5 CREATE INDEX test_v_idx3 ON test_kv(w) STORING(v); 6 CREATE TABLE test_kvr1(k INT PRIMARY KEY REFERENCES test_kv(k)); 7 CREATE TABLE test_kvr2(k INT, v INT UNIQUE REFERENCES test_kv(k)); 8 CREATE TABLE test_kvr3(k INT, v INT UNIQUE REFERENCES test_kv(v)); 9 CREATE TABLE test_kvi1(k INT PRIMARY KEY) INTERLEAVE IN PARENT test_kv(k); 10 CREATE TABLE test_kvi2(k INT PRIMARY KEY, v INT); 11 CREATE UNIQUE INDEX test_kvi2_idx ON test_kvi2(v) INTERLEAVE IN PARENT test_kv(v); 12 CREATE VIEW test_v1 AS SELECT v FROM test_kv; 13 CREATE VIEW test_v2 AS SELECT v FROM test_v1; 14 15 query ITITTBTB colnames 16 SELECT * FROM crdb_internal.table_columns WHERE descriptor_name LIKE 'test_%' ORDER BY descriptor_id, column_id 17 ---- 18 descriptor_id descriptor_name column_id column_name column_type nullable default_expr hidden 19 53 test_kv 1 k family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false false NULL false 20 53 test_kv 2 v family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false true NULL false 21 53 test_kv 3 w family:DecimalFamily width:0 precision:0 locale:"" visible_type:0 oid:1700 time_precision_is_set:false true NULL false 22 54 test_kvr1 1 k family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false false NULL false 23 55 test_kvr2 1 k family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false true NULL false 24 55 test_kvr2 2 v family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false true NULL false 25 55 test_kvr2 3 rowid family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false false unique_rowid() true 26 56 test_kvr3 1 k family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false true NULL false 27 56 test_kvr3 2 v family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false true NULL false 28 56 test_kvr3 3 rowid family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false false unique_rowid() true 29 57 test_kvi1 1 k family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false false NULL false 30 58 test_kvi2 1 k family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false false NULL false 31 58 test_kvi2 2 v family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false true NULL false 32 59 test_v1 1 v family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false false NULL false 33 60 test_v2 1 v family:IntFamily width:64 precision:0 locale:"" visible_type:0 oid:20 time_precision_is_set:false false NULL false 34 35 query ITITTBB colnames 36 SELECT * FROM crdb_internal.table_indexes WHERE descriptor_name LIKE 'test_%' ORDER BY descriptor_id, index_id 37 ---- 38 descriptor_id descriptor_name index_id index_name index_type is_unique is_inverted 39 53 test_kv 1 primary primary true false 40 53 test_kv 2 test_v_idx secondary true false 41 53 test_kv 3 test_v_idx2 secondary false false 42 53 test_kv 4 test_v_idx3 secondary false false 43 54 test_kvr1 1 primary primary true false 44 55 test_kvr2 1 primary primary true false 45 55 test_kvr2 2 test_kvr2_v_key secondary true false 46 56 test_kvr3 1 primary primary true false 47 56 test_kvr3 2 test_kvr3_v_key secondary true false 48 57 test_kvi1 1 primary primary true false 49 58 test_kvi2 1 primary primary true false 50 58 test_kvi2 2 test_kvi2_idx secondary true false 51 59 test_v1 0 · primary false false 52 60 test_v2 0 · primary false false 53 54 query ITITTITT colnames 55 SELECT * FROM crdb_internal.index_columns WHERE descriptor_name LIKE 'test_%' ORDER BY descriptor_id, index_id, column_type, column_id 56 ---- 57 descriptor_id descriptor_name index_id index_name column_type column_id column_name column_direction 58 53 test_kv 1 primary key 1 k ASC 59 53 test_kv 2 test_v_idx extra 1 NULL NULL 60 53 test_kv 2 test_v_idx key 2 v ASC 61 53 test_kv 3 test_v_idx2 extra 1 NULL NULL 62 53 test_kv 3 test_v_idx2 key 2 v DESC 63 53 test_kv 3 test_v_idx2 storing 3 NULL NULL 64 53 test_kv 4 test_v_idx3 composite 3 NULL NULL 65 53 test_kv 4 test_v_idx3 extra 1 NULL NULL 66 53 test_kv 4 test_v_idx3 key 3 w ASC 67 53 test_kv 4 test_v_idx3 storing 2 NULL NULL 68 54 test_kvr1 1 primary key 1 k ASC 69 55 test_kvr2 1 primary key 3 rowid ASC 70 55 test_kvr2 2 test_kvr2_v_key extra 3 NULL NULL 71 55 test_kvr2 2 test_kvr2_v_key key 2 v ASC 72 56 test_kvr3 1 primary key 3 rowid ASC 73 56 test_kvr3 2 test_kvr3_v_key extra 3 NULL NULL 74 56 test_kvr3 2 test_kvr3_v_key key 2 v ASC 75 57 test_kvi1 1 primary key 1 k ASC 76 58 test_kvi2 1 primary key 1 k ASC 77 58 test_kvi2 2 test_kvi2_idx extra 1 NULL NULL 78 58 test_kvi2 2 test_kvi2_idx key 2 v ASC 79 80 query ITIIITITT colnames 81 SELECT * FROM crdb_internal.backward_dependencies WHERE descriptor_name LIKE 'test_%' ORDER BY descriptor_id, index_id, dependson_type, dependson_id, dependson_index_id 82 ---- 83 descriptor_id descriptor_name index_id column_id dependson_id dependson_type dependson_index_id dependson_name dependson_details 84 54 test_kvr1 NULL NULL 53 fk 1 fk_k_ref_test_kv NULL 85 55 test_kvr2 NULL NULL 53 fk 1 fk_v_ref_test_kv NULL 86 56 test_kvr3 NULL NULL 53 fk 2 fk_v_ref_test_kv NULL 87 57 test_kvi1 1 NULL 53 interleave 1 NULL SharedPrefixLen: 1 88 58 test_kvi2 2 NULL 53 interleave 1 NULL SharedPrefixLen: 1 89 59 test_v1 NULL NULL 53 view NULL NULL NULL 90 60 test_v2 NULL NULL 59 view NULL NULL NULL 91 92 query ITIITITT colnames 93 SELECT * FROM crdb_internal.forward_dependencies WHERE descriptor_name LIKE 'test_%' ORDER BY descriptor_id, index_id, dependedonby_type, dependedonby_id, dependedonby_index_id 94 ---- 95 descriptor_id descriptor_name index_id dependedonby_id dependedonby_type dependedonby_index_id dependedonby_name dependedonby_details 96 53 test_kv NULL 54 fk NULL NULL NULL 97 53 test_kv NULL 55 fk NULL NULL NULL 98 53 test_kv NULL 56 fk NULL NULL NULL 99 53 test_kv NULL 59 view 0 NULL Columns: [1 2 3] 100 53 test_kv 1 57 interleave 1 NULL SharedPrefixLen: 0 101 53 test_kv 1 58 interleave 2 NULL SharedPrefixLen: 0 102 59 test_v1 NULL 60 view 0 NULL Columns: [1] 103 104 # Checks view dependencies (#17306) 105 statement ok 106 CREATE TABLE moretest_t(k INT, v INT); 107 CREATE VIEW moretest_v AS SELECT v FROM moretest_t WHERE FALSE 108 109 query ITIIITITT colnames 110 SELECT * FROM crdb_internal.backward_dependencies WHERE descriptor_name LIKE 'moretest_%' ORDER BY descriptor_id, index_id, dependson_type, dependson_id, dependson_index_id 111 ---- 112 descriptor_id descriptor_name index_id column_id dependson_id dependson_type dependson_index_id dependson_name dependson_details 113 62 moretest_v NULL NULL 61 view NULL NULL NULL 114 115 query ITIITITT colnames 116 SELECT * FROM crdb_internal.forward_dependencies WHERE descriptor_name LIKE 'moretest_%' ORDER BY descriptor_id, index_id, dependedonby_type, dependedonby_id, dependedonby_index_id 117 ---- 118 descriptor_id descriptor_name index_id dependedonby_id dependedonby_type dependedonby_index_id dependedonby_name dependedonby_details 119 61 moretest_t NULL 62 view 0 NULL Columns: [1 2 3] 120 121 # Check sequence dependencies. 122 123 statement ok 124 CREATE SEQUENCE blog_posts_id_seq 125 126 statement ok 127 CREATE TABLE blog_posts (id INT PRIMARY KEY DEFAULT nextval('blog_posts_id_seq'), title text) 128 129 query ITIIITITT colnames 130 SELECT * FROM crdb_internal.backward_dependencies WHERE descriptor_name LIKE 'blog_posts' 131 ---- 132 descriptor_id descriptor_name index_id column_id dependson_id dependson_type dependson_index_id dependson_name dependson_details 133 64 blog_posts NULL 1 63 sequence NULL NULL NULL 134 135 query ITIITITT colnames 136 SELECT * FROM crdb_internal.forward_dependencies WHERE descriptor_name LIKE 'blog_posts%' 137 ---- 138 descriptor_id descriptor_name index_id dependedonby_id dependedonby_type dependedonby_index_id dependedonby_name dependedonby_details 139 63 blog_posts_id_seq NULL 64 sequence 0 NULL Columns: [1]