github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/drop_index (about)

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE TABLE users (
     5    id    INT PRIMARY KEY,
     6    name  VARCHAR NOT NULL,
     7    title VARCHAR,
     8    INDEX foo (name),
     9    UNIQUE INDEX bar (id, name),
    10    INDEX baw (name, title)
    11  )
    12  
    13  statement ok
    14  CREATE TABLE othertable (
    15     x INT,
    16     y INT,
    17     INDEX baw (x),
    18     INDEX yak (y, x)
    19  )
    20  
    21  statement error index name "baw" is ambiguous
    22  DROP INDEX baw
    23  
    24  statement error index name "baw" is ambiguous
    25  DROP INDEX IF EXISTS baw
    26  
    27  statement error index "ark" does not exist
    28  DROP INDEX ark
    29  
    30  statement ok
    31  DROP INDEX IF EXISTS ark
    32  
    33  statement error index "ark" does not exist
    34  DROP INDEX users@ark
    35  
    36  statement ok
    37  DROP INDEX IF EXISTS users@ark
    38  
    39  statement ok
    40  DROP INDEX yak
    41  
    42  statement ok
    43  CREATE INDEX yak ON othertable (y, x)
    44  
    45  statement ok
    46  DROP INDEX IF EXISTS yak
    47  
    48  statement ok
    49  DROP TABLE othertable
    50  
    51  statement ok
    52  DROP INDEX baw
    53  
    54  statement ok
    55  INSERT INTO users VALUES (1, 'tom', 'cat'),(2, 'jerry', 'rat')
    56  
    57  query TTBITTBB colnames
    58  SHOW INDEXES FROM users
    59  ----
    60  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
    61  users       primary     false       1             id           ASC        false    false
    62  users       foo         true        1             name         ASC        false    false
    63  users       foo         true        2             id           ASC        false    true
    64  users       bar         false       1             id           ASC        false    false
    65  users       bar         false       2             name         ASC        false    false
    66  
    67  statement error index "zap" does not exist
    68  DROP INDEX users@zap
    69  
    70  statement ok
    71  DROP INDEX IF EXISTS users@zap
    72  
    73  query TTBITTBB colnames
    74  SHOW INDEXES FROM users
    75  ----
    76  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
    77  users       primary     false       1             id           ASC        false    false
    78  users       foo         true        1             name         ASC        false    false
    79  users       foo         true        2             id           ASC        false    true
    80  users       bar         false       1             id           ASC        false    false
    81  users       bar         false       2             name         ASC        false    false
    82  
    83  # Also test that dropping with a non-existing index still drops 'foo'.
    84  
    85  statement ok
    86  DROP INDEX IF EXISTS users@foo, users@zap
    87  
    88  query TTBITTBB colnames
    89  SHOW INDEXES FROM users
    90  ----
    91  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
    92  users       primary     false       1             id           ASC        false    false
    93  users       bar         false       1             id           ASC        false    false
    94  users       bar         false       2             name         ASC        false    false
    95  
    96  user testuser
    97  
    98  statement error user testuser does not have CREATE privilege on relation users
    99  DROP INDEX users@bar
   100  
   101  user root
   102  
   103  statement ok
   104  GRANT CREATE ON TABLE users TO testuser
   105  
   106  user testuser
   107  
   108  statement error in use as unique constraint
   109  DROP INDEX users@bar
   110  
   111  statement error in use as unique constraint
   112  DROP INDEX users@bar RESTRICT
   113  
   114  statement ok
   115  DROP INDEX users@bar CASCADE
   116  
   117  query TTBITTBB colnames
   118  SHOW INDEXES FROM users
   119  ----
   120  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
   121  users       primary     false       1             id           ASC        false    false
   122  
   123  user root
   124  
   125  query ITT rowsort
   126  SELECT * FROM users
   127  ----
   128  1 tom   cat
   129  2 jerry rat
   130  
   131  statement ok
   132  CREATE INDEX foo ON users (name)
   133  
   134  statement ok
   135  CREATE INDEX bar ON users (title)
   136  
   137  statement ok
   138  CREATE INDEX baz ON users (name, title)
   139  
   140  statement ok
   141  DROP INDEX IF EXISTS users@invalid, users@baz
   142  
   143  query TTBITTBB colnames
   144  SHOW INDEXES FROM users
   145  ----
   146  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
   147  users       primary     false       1             id           ASC        false    false
   148  users       foo         true        1             name         ASC        false    false
   149  users       foo         true        2             id           ASC        false    true
   150  users       bar         true        1             title        ASC        false    false
   151  users       bar         true        2             id           ASC        false    true
   152  
   153  statement ok
   154  CREATE VIEW v AS SELECT name FROM users@{FORCE_INDEX=foo}
   155  
   156  statement error cannot drop index "foo" because view "v" depends on it
   157  DROP INDEX users@foo
   158  
   159  statement ok
   160  DROP INDEX users@bar
   161  
   162  query TTBITTBB colnames
   163  SHOW INDEXES FROM users
   164  ----
   165  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
   166  users       primary     false       1             id           ASC        false    false
   167  users       foo         true        1             name         ASC        false    false
   168  users       foo         true        2             id           ASC        false    true
   169  
   170  statement ok
   171  CREATE VIEW v2 AS SELECT name FROM v
   172  
   173  query TTT
   174  SHOW TABLES
   175  ----
   176  public  users  table
   177  public  v      view
   178  public  v2     view
   179  
   180  statement ok
   181  GRANT ALL ON users to testuser
   182  
   183  statement ok
   184  GRANT ALL ON v to testuser
   185  
   186  user testuser
   187  
   188  statement error user testuser does not have DROP privilege on relation v2
   189  DROP INDEX users@foo CASCADE
   190  
   191  user root
   192  
   193  statement ok
   194  DROP INDEX users@foo CASCADE
   195  
   196  query TTBITTBB colnames
   197  SHOW INDEXES FROM users
   198  ----
   199  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
   200  users       primary     false       1             id           ASC        false    false
   201  
   202  query TTT
   203  SHOW TABLES
   204  ----
   205  public  users  table
   206  
   207  # Test the syntax without a '@'
   208  
   209  statement ok
   210  CREATE INDEX baz ON users (name)
   211  
   212  # Also test that dropping with a non-existing index still drops 'baz'.
   213  
   214  statement ok
   215  DROP INDEX IF EXISTS baz, zap
   216  
   217  query TTBITTBB colnames
   218  SHOW INDEXES FROM users
   219  ----
   220  table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit
   221  users       primary     false       1             id           ASC        false    false
   222  
   223  # Test that it still succeeds when an index does not exist.
   224  
   225  statement ok
   226  DROP INDEX IF EXISTS baz
   227  
   228  # Test that presence of a view or sequence doesn't break DROP INDEX (#21834)
   229  
   230  statement ok
   231  CREATE DATABASE view_test
   232  
   233  statement ok
   234  SET DATABASE = view_test
   235  
   236  statement ok
   237  CREATE TABLE t (id INT)
   238  
   239  statement ok
   240  CREATE VIEW v AS SELECT id FROM t
   241  
   242  statement error pgcode 42704 pq: index "nonexistent_index" does not exist
   243  DROP INDEX nonexistent_index
   244  
   245  statement ok
   246  CREATE DATABASE sequence_test
   247  
   248  statement ok
   249  SET DATABASE = sequence_test
   250  
   251  statement ok
   252  CREATE SEQUENCE s
   253  
   254  statement error pgcode 42704 pq: index "nonexistent_index" does not exist
   255  DROP INDEX nonexistent_index
   256  
   257  statement ok
   258  CREATE TABLE tu (a INT UNIQUE)
   259  
   260  statement ok
   261  CREATE UNIQUE INDEX tu_a ON tu(a)
   262  
   263  statement error in use as unique constraint
   264  DROP INDEX tu_a_key
   265  
   266  statement ok
   267  DROP INDEX tu_a
   268  
   269  # Test that we have more relaxed restrictions on dropping indexes referenced by fks.
   270  subtest fk_drop
   271  
   272  # Ensure that we can drop an index used by a foreign key if there is another
   273  # index that can take its place.
   274  statement ok
   275  CREATE TABLE fk1 (x INT);
   276  CREATE TABLE fk2 (x INT PRIMARY KEY);
   277  ALTER TABLE fk1 ADD CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES fk2 (x);
   278  CREATE INDEX i ON fk1 (x);
   279  DROP INDEX fk1_auto_index_fk1
   280  
   281  statement error pq: index "i" is in use as a foreign key constraint
   282  DROP INDEX fk1@i
   283  
   284  # Ensure that DROP INDEX CASCADE does not delete the foreign key when
   285  # there is another index that can satisfy the foreign key constraint.
   286  statement ok
   287  DROP TABLE fk1;
   288  DROP TABLE fk2;
   289  CREATE TABLE fk1 (x int);
   290  CREATE TABLE fk2 (x int PRIMARY KEY);
   291  CREATE INDEX i ON fk1 (x);
   292  CREATE INDEX i2 ON fk1 (x);
   293  ALTER TABLE fk1 ADD CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES fk2 (x);
   294  DROP INDEX fk1@i CASCADE
   295  
   296  query TT
   297  SHOW CREATE fk1
   298  ----
   299  fk1  CREATE TABLE fk1 (
   300       x INT8 NULL,
   301       CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES fk2(x),
   302       INDEX i2 (x ASC),
   303       FAMILY "primary" (x, rowid)
   304  )
   305  
   306  # Ensure that now the cascade deletes the foreign key constraint.
   307  statement ok
   308  DROP INDEX fk1@i2 CASCADE
   309  
   310  query TT
   311  SHOW CREATE fk1
   312  ----
   313  fk1  CREATE TABLE fk1 (
   314       x INT8 NULL,
   315       FAMILY "primary" (x, rowid)
   316  )
   317  
   318  # test that notices are generated on index drops
   319  subtest notice_on_drop_index
   320  
   321  query T noticetrace
   322  CREATE TABLE drop_index_test(a int); CREATE INDEX drop_index_test_index ON drop_index_test(a); DROP INDEX drop_index_test_index
   323  ----
   324  NOTICE: the data for dropped indexes is reclaimed asynchronously
   325  HINT: The reclamation delay can be customized in the zone configuration for the table.