psql创建order与order_products一对多关系时出现语法错误

触发syntax error at or near "order"错误的核心原因是**order属于SQL标准保留关键字**（对应ORDER BY排序语法的关键字）：

• 你创建order表时用双引号包裹了表名，将其声明为了自定义标识符
• 但在order_products表写外键关联时，REFERENCES order(id)里的order没有加双引号，数据库解析时会把它识别成排序关键字，而非你创建的自定义表，直接抛出语法错误。

二选一即可，优先推荐第一种，能避免后续所有SQL操作都要特殊处理表名的麻烦：

• 方案1（推荐）：将表名order修改为非保留字的orders，同步修改所有关联位置的表名引用。后续写SQL无需额外给表名加引号，也不会出现因为漏写引号触发的同类语法错误，维护成本更低。
• 方案2：保留order作为表名，所有引用该表的位置都必须和建表时一致，用双引号包裹表名。

CREATE TABLE IF NOT EXISTS "customers" (
  "id" SERIAL PRIMARY KEY,
  "first_name" varchar,
  "last_name" varchar,
  "username" varchar,
  "password" varchar,
  "email" varchar UNIQUE,
  "is_admin" boolean 
);

CREATE TABLE IF NOT EXISTS "address" (
  "customer_id" int REFERENCES customers(id),
  "zipcode" int,
  "country" varchar,
  "city" varchar,
  "street_name" varchar,
  "stree_number" varchar,
  "mobile_number" int
);

CREATE TABLE IF NOT EXISTS "products" (
  "id" SERIAL PRIMARY KEY,
  "item_name" varchar UNIQUE,
  "description" text,
  "image_url" varchar,
  "price" float
);

CREATE TABLE IF NOT EXISTS "order" (
  "id" SERIAL PRIMARY KEY,
  "customer_id" int REFERENCES customers(id),
  "date_of_purchase" date,
  "total_price" int
);

CREATE TABLE IF NOT EXISTS "order_products" (
  "order_id" int REFERENCES "order"(id),
  "product_id" int REFERENCES products(id),
  "quantity" int,
  "total_price" int,
  UNIQUE(order_id, product_id)
);

CREATE TABLE IF NOT EXISTS "cart" (
  "id" SERIAL PRIMARY KEY,
  "customer_id" int REFERENCES customers(id),
  "date_of_purchase" date,
  "total_price" int
);

CREATE TABLE IF NOT EXISTS "cart_products" (
  "cart_id" int REFERENCES cart(id),
  "product_id" int REFERENCES products(id),
  "quantity" int,
  "total_price" int,
  UNIQUE(cart_id, product_id)
);

注：上面的SQL同时调整了order、cart表的字段顺序，将主键id放在字段列表第一列，符合常规建表习惯，不影响功能逻辑。

内容的提问来源于stack exchange，提问作者Csaba burján