使用UCanAccess始终生成身份，生成错误的表架构 [英] GENERATED ALWAYS AS IDENTITY produces incorrect table schema with UCanAccess

问题描述

我正在使用JDBC/UCanAccess编写程序，发现我在创建其中一个表时发现，而不是创建具有DateTime格式的四列，它还将相同的格式应用于下一列，而将另一列替换格式:

I'm writing a program using JDBC/UCanAccess and I'm finding when I create one of the tables, instead of creating four columns with the DateTime format, it also applies the same format to the next column, displacing the other formats:

CREATE TABLE Person (
    Id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
    Name VARCHAR(40), 
    Surname VARCHAR(40), 
    Card VARCHAR(9), 
    Email VARCHAR(30)
);

CREATE TABLE Place (
    Id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
    Name VARCHAR(40)
);


CREATE TABLE Activity (
    Id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
    Name VARCHAR(40) NOT NULL, 
    Description1 VARCHAR(500), 
    Description2 VARCHAR(500), 
    Id_Person INT, 
    Hour_Start DATETIME, 
    Hour_End DATETIME, 
    Date_Plan_Start DATETIME, 
    Date_Plan_End DATETIME, 
    Cost CURRENCY, 
    Sale CURRENCY, 
    Id_Place INT, 
    CONSTRAINT fk_person_activity FOREIGN KEY (Id_Person) REFERENCES Person (Id), 
    CONSTRAINT fk_place_activity FOREIGN KEY (Id_Place) REFERENCES Place (Id)
);

似乎可以完成所有工作，直到Date_Plan_End，但是创建的表的Cost为DateTime，Id_Place为Currency.如果有人能告诉我为什么会发生这种情况，我将不胜感激.

It seems to do everything as it should up to Date_Plan_End, but the created table has Cost as a DateTime, and Id_Place as Currency. If anyone could tell me why this happens I'd appreciate it.

仅插入此错误仍然发生:

The error stills happens inserting only this:

CREATE TABLE Activity (
    Id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
    Name VARCHAR(40) NOT NULL, 
    Date_Plan_End DATETIME, 
    Cost CURRENCY, 
    Sale CURRENCY, 
    Id_Place INT, 
    PRIMARY KEY (Id)
);

SQL和结果

该问题似乎与始终以身份生成"(从1开始，以1递增)有关.尽管我不知道为什么将其移除，但仍阻止了位移的发生.

The problem seems to be related to the GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1). Its removal stops the displacement from happening, although I don't know why.

推荐答案

该问题似乎与始终以身份生成"(从1开始，以1递增)有关.移除后，便不会发生位移

The problem seems to be related to the GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1). Its removal stops the displacement from happening

这是因为... GENERATED ALWAYS AS IDENTITY ...是HSQLDB DDL语法，而UCanAccess使用Access SQL DDL语法.因此，代替

That's because ... GENERATED ALWAYS AS IDENTITY ... is HSQLDB DDL syntax, and UCanAccess uses Access SQL DDL syntax. Therefore instead of

CREATE TABLE Activity (
    Id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
    Name VARCHAR(40) NOT NULL, 
    Date_Plan_End DATETIME, 
    Cost CURRENCY, 
    Sale CURRENCY, 
    Id_Place INT, 
    PRIMARY KEY (Id)
);

您应该使用

CREATE TABLE Activity (
    Id COUNTER PRIMARY KEY, 
    Name VARCHAR(40) NOT NULL, 
    Date_Plan_End DATETIME, 
    Cost CURRENCY, 
    Sale CURRENCY, 
    Id_Place INT
);

这篇关于使用UCanAccess始终生成身份，生成错误的表架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！