test/altercons2.test - master - niceSOFT/sqlite

test/altercons2.test
297 строк7 KB
dan
Allow "ALTER TABLE ... DROP CONSTRAINT" to drop foreign key constraints.
08 июл 2026, 20:40
08 июл 2026, 20:4047a4302
# 2025 September 18
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix altercons2

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
  finish_test
  return
}

foreach {tn newsql alter res final} {
  1 "CREATE TABLE t1(a, b"
    "ALTER TABLE t1 ALTER c SET NOT NULL"
    {1 {database disk image is malformed}}
    "CREATE TABLE t1(a, b"

  2 "CREATE TABLE t1(a, b, "
    "ALTER TABLE t1 ALTER c DROP NOT NULL"
    {0 {}}
    "CREATE TABLE t1(a, b, "

  3 "CREATE TABLE t1(a, b, CHECK( ..."
    "ALTER TABLE t1 ALTER c DROP NOT NULL"
    {0 {}}
    "CREATE TABLE t1(a, b, CHECK( ..."

  4 "CREATE TABLE t1(a, b, c NOT NULL"
    "ALTER TABLE t1 ALTER c DROP NOT NULL"
    {0 {}}
    "CREATE TABLE t1(a, b, c "

  5 "CREATE TABLE"
    "ALTER TABLE t1 ALTER c DROP NOT NULL"
    {1 {database disk image is malformed}}
    "CREATE TABLE"

  6 "CREATE TABLE"
    "ALTER TABLE t1 ADD CONSTRAINT nn CHECK (a!=0)"
    {1 {database disk image is malformed}}
    "CREATE TABLE"

} {
  reset_db
  do_execsql_test 1.$tn.0 {
    CREATE TABLE t1(a, b, c NOT NULL, CONSTRAINT xyz CHECK( a!=0 ));
  }
  do_execsql_test 1.$tn.1 {
    PRAGMA writable_schema = 1;
    UPDATE sqlite_schema SET sql = $::newsql
  }
  do_catchsql_test 1.$tn.2 $alter $res

  do_execsql_test 1.$tn.3 {
    SELECT sql FROM sqlite_schema WHERE name='t1'
  } [list $final]
}

#-------------------------------------------------------------------------

reset_db
proc xAuth {t args} {
  if {$t=="SQLITE_ALTER_TABLE"} {
    return "SQLITE_DENY"
  }
  return "SQLITE_OK"
}
sqlite3 db test.db
db auth xAuth

do_execsql_test 2.0 {
  CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c);
}

do_catchsql_test 2.1.1 {
  ALTER TABLE x1 ADD CONSTRAINT ccc CHECK (a!='a')
} {1 {not authorized}}
do_execsql_test 2.1.2 {
  SELECT sql FROM sqlite_schema WHERE name='x1'
} {{CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c)}}

do_catchsql_test 2.2.1 {
  ALTER TABLE x1 ALTER c SET NOT NULL
} {1 {not authorized}}
do_execsql_test 2.2.2 {
  SELECT sql FROM sqlite_schema WHERE name='x1'
} {{CREATE TABLE x1(a PRIMARY KEY, b CHECK(a!=b) NOT NULL, c)}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(x);
}
do_execsql_test 3.1 {
  ALTER TABLE t1 ALTER x SET NOT NULL;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE abc(a, b, c, CONSTRAINT one CONSTRAINT two CHECK (b!=c));
}
do_execsql_test 4.1 {
  ALTER TABLE abc DROP CONSTRAINT one
}
do_execsql_test 4.2 {
  SELECT sql FROM sqlite_schema
} {
  {CREATE TABLE abc(a, b, c, CONSTRAINT two CHECK (b!=c))}
}

#-------------------------------------------------------------------------
reset_db

# The columns must come before the table constraints in a CREATE TABLE
# statement. This is useful, as it means the DROP CONSTRAINT code does
# not have to handle the constraint immediately following the '(' at
# the start of the column-list.
do_catchsql_test 5.0 {
  CREATE TABLE abc(a, b, c, CONSTRAINT two CHECK (b!=c), d)
} {1 {near "d": syntax error}}
do_catchsql_test 5.1 {
  CREATE TABLE def(CONSTRAINT abc CHECK( b!=c ), a, b, c);
} {1 {near "CONSTRAINT": syntax error}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE abc(a, b CONSTRAINT two COLLATE nocase CHECK (a!=b), c CONSTRAINT one DEFAULT 'abc');
}

do_execsql_test 6.1 {
  ALTER TABLE abc DROP CONSTRAINT one;
  ALTER TABLE abc DROP CONSTRAINT two;
}

do_execsql_test 6.2 {
  SELECT sql FROM sqlite_schema
} {
  {CREATE TABLE abc(a, b COLLATE nocase CHECK (a!=b), c DEFAULT 'abc')}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 7.0 {
  CREATE TABLE abc(a, b, c, CONSTRAINT one CHECK (a>b) FOREIGN KEY(a) REFERENCES abc);
}
do_execsql_test 7.1 {
  ALTER TABLE abc DROP CONSTRAINT one
}
do_execsql_test 7.2 {
  SELECT sql FROM sqlite_schema
} {
  {CREATE TABLE abc(a, b, c, FOREIGN KEY(a) REFERENCES abc)}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 8.0 {
  CREATE TABLE abc(a, b, c, CONSTRAINT one FOREIGN KEY(a) REFERENCES abc);
}
do_catchsql_test 8.1 {
  ALTER TABLE abc DROP CONSTRAINT one
} {0 {}}
do_execsql_test 8.2 {
  SELECT sql FROM sqlite_schema
} {{CREATE TABLE abc(a, b, c)}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 9.0 {
  CREATE TABLE abc(a, b NOT NULL AS (a+1))
}
do_execsql_test 9.1 {
  ALTER TABLE abc ALTER b DROP NOT NULL;
  SELECT sql FROM sqlite_schema;
} {{CREATE TABLE abc(a, b AS (a+1))}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 10.0 {
  CREATE TABLE abc(a, b GENERATED ALWAYS AS (a+1));
  INSERT INTO abc VALUES(1), (2);
  SELECT * FROM abc;
} {1 2   2 3}

do_execsql_test 10.1 {
  ALTER TABLE abc ALTER b SET NOT NULL;
}
do_catchsql_test 10.2 {
  INSERT INTO abc VALUES(NULL);
} {1 {NOT NULL constraint failed: abc.b}}
do_execsql_test 10.3 {
  INSERT INTO abc VALUES(3);
  ALTER TABLE abc ALTER COLUMN b DROP NOT NULL;
}
do_execsql_test 10.4 {
  INSERT INTO abc VALUES(NULL);
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 11.0 {
  CREATE TABLE t1(a, b, c);
}

do_execsql_test 11.1.1 {
  ALTER TABLE t1 ADD CONSTRAINT c1 CHECK(a=b) --comment
  ;
}

do_execsql_test 11.1.2 {ALTER TABLE t1 ADD CONSTRAINT c2 CHECK(a=b) --comment}

do_execsql_test 11.1.3 {
  SELECT sql FROM sqlite_schema;
} {
  {CREATE TABLE t1(a, b, c, CONSTRAINT c1 CHECK(a=b), CONSTRAINT c2 CHECK(a=b))}
}

do_execsql_test 11.2.1 {
  CREATE TABLE t2(a, b);
}
do_execsql_test 11.2.2 {ALTER TABLE t2 ALTER b SET NOT NULL --new cons}
do_execsql_test 11.2.3 {
  SELECT sql FROM sqlite_schema WHERE name='t2';
} {
  {CREATE TABLE t2(a, b NOT NULL)}
}
do_execsql_test 11.2.3 {
  ALTER TABLE t2 ALTER b DROP NOT NULL;
  SELECT sql FROM sqlite_schema WHERE name='t2';
} {
  {CREATE TABLE t2(a, b)}
}
do_execsql_test 11.2.2 {ALTER TABLE t2 ALTER b SET NOT NULL --new cons
;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 12.0 {
  CREATE TABLE "Test" ( 
      "IsActive" INTEGER, 
      CONSTRAINT "BooleanZeroOrOne" CHECK ("IsActive" IN (0, 1)) 
  );
}

do_execsql_test 12.1 {
  ALTER TABLE Test DROP CONSTRAINT BooleanZeroOrOne
}

do_execsql_test 12.2 {
  SELECT sql FROM sqlite_schema
} {
  {CREATE TABLE "Test" ( 
      "IsActive" INTEGER)}
}

do_execsql_test 12.3 {
  DROP TABLE Test;
  CREATE TABLE "Test" ( 
      "IsActive" INTEGER, 
      CONSTRAINT "BooleanZeroOrOne" CHECK ("IsActive" IN (0, 1)) 
  );
}

do_execsql_test 12.4 {
  ALTER TABLE Test DROP CONSTRAINT "BooleanZeroOrOne"
}

do_execsql_test 12.5 {
  SELECT sql FROM sqlite_schema
} {
  {CREATE TABLE "Test" ( 
      "IsActive" INTEGER)}
}

do_execsql_test 12.6 {
  CREATE TABLE t1(a, b CONSTRAINT "a""b" CHECK (b IS NOT NULL));
}

do_execsql_test 12.7 {
  ALTER TABLE t1 DROP CONSTRAINT "a""b"
}

finish_test