SQL建表代码报第15、25行附近语法错误，求错误原因及修复方案

错误原因汇总 #

• 字段长度定义位置错误：SQL语法要求长度标识跟在数据类型后，而非字段名后，比如publisherName(20) TEXT错误，正确写法为publisherName TEXT(20)
• gameName字段的CHECK约束为空，未填写校验规则，属于语法错误
• 字符串常量错误使用双引号包裹：SQL规范中字符串需用单引号，双引号默认识别为表名、字段名等标识符，会导致IN条件语法报错
• 建表顺序错误：games表设置了关联publishers表的外键，需要先创建父表publishers，再创建子表games
• publishers表的publisherWeb字段缺失数据类型定义，仅标注了长度，属于语法错误
• 可选优化点：SQLite原生无NUMBER、MONEY数据类型，可替换为INTEGER、REAL类型保证兼容性

修正后完整代码 #

.echo on
.mode column 
.headers on

PRAGMA foreign_keys = ON;

.open ComputerGames.db

-- 删表顺序保持不变，先删子表再删父表
DROP TABLE IF EXISTS games;
DROP TABLE IF EXISTS publishers;

-- 先创建父表publishers
CREATE TABLE publishers (
  publisherName TEXT(20) PRIMARY KEY NOT NULL,
  publisherFounded DATE NOT NULL,
  publisherCountry TEXT(15) NOT NULL,
  publisherCity TEXT(15) NOT NULL, 
  publisherPhone TEXT(15),
  publisherWeb TEXT(25)
);

-- 再创建子表games
CREATE TABLE games (
  gameID INTEGER PRIMARY KEY NOT NULL,
  -- 补充gameName的非空校验，也可根据需求修改规则
  gameName TEXT(20) NOT NULL CHECK (length(gameName) > 0),
  publisherName TEXT(20) NOT NULL CHECK (publisherName in ('Rockstar', 'Bethesda','Valve','Superhot team', 'Mojang', 'Epic games', 'Bungie','Nintendo')) REFERENCES publishers (publisherName),
  gameReleaseDate DATE NOT NULL,
  gameGenre TEXT(10) NOT NULL CHECK (gameGenre in ('RPG','Simulation','Shooter','Sports')),
  gameAgeRating INTEGER NOT NULL CHECK (gameAgeRating in(3,7,12,15,18)),
  gameRRP REAL NOT NULL CHECK (gameRRP >= 0)
);

内容的提问来源于stack exchange，提问作者jasmin