DBeaver中SQL代码执行异常：无法创建全表且报语法错误

主要错误点 #

• 表创建顺序不合理：project表先创建，但它的外键依赖budget和division表，这两个表在后续才创建，导致外键约束无法正常生效。需调整创建顺序，先建被依赖的基础表。
• 列名格式错误：多个表中出现带空格的列名（如committee id、office id），SQL列名不能直接用空格，需改为下划线连接的格式（如committee_id）。
• 拼写错误：
  • committee表主键约束中的committe_id少一个字母e，应为committee_id；
  • section表外键约束名中的buget拼写错误，应为budget；
  • budget_charge表外键引用写为budget(budget)，正确应为budget(budget_id)；
  • committee_member表约束名中的comittee少一个字母m，应为committee。
• 约束名重复：多个表的主键和外键使用了相同名称（比如employee_job_assignment的两个外键），SQL要求约束名必须唯一，需为每个约束设置不同名称。
• 关键字缺失：CREATE office语句缺少table关键字，正确应为CREATE table office。
• 未设置主键：employee_office、project_employee这类多对多关联表未设置主键，通常需设置复合主键。
• 语法遗漏：committee_member表的两个约束之间缺少逗号分隔。

修正后的完整SQL代码 #

-- 先创建被依赖的基础表
CREATE table division( 
    division_id  int not null,
    division_name varchar(40),
    
    constraint pk_division_division_id primary key(division_id)
);

CREATE  table budget(
    budget_id int not null,
    budget_description varchar(100),
    
    constraint pk_budget_budget_id primary key(budget_id)
);

CREATE table committee(
    committee_id int not null,
    committee_name varchar(40),
    
    constraint pk_committee_committee_id primary key(committee_id)
);

create table employee( 
    employee_id int not null,
    given_name varchar(40),
    middle_name varchar(40),
    family_name varchar(40),
    form_of_address varchar(10),
    name_suffix varchar(25),
    work_phone_number varchar(20),
    hourly_budget_rate numeric(8,2),
    
    constraint pk_employee_employee_id primary key(employee_id)
);

CREATE table project (
    project_id int not null,
    budget_id int not null,
    division_id int not null,
    project_name varchar(40),
    project_begin_date date,
    project_end_date date,
    
    constraint pk_project_project_id primary key(project_id),
    constraint fk_project_budget_id foreign key(budget_id) references budget(budget_id),
    constraint fk_project_division_id foreign key (division_id) references division(division_id)
);

CREATE  table section(
    section_id int not null,
    division_id int,
    budget_id int,
    section_name varchar(40),
    
    constraint pk_section_section_id primary key(section_id),
    constraint fk_section_budget_id foreign key(budget_id) references budget(budget_id),
    constraint fk_section_division_id foreign key(division_id) references division(division_id)
);

CREATE table employee_job_assignment( 
    job_assignment_id int not null,
    employee_id int not null,
    section_id int not null,
    begin_datetime date,
    end_datetime date,
    job_title varchar(100),
    
    constraint pk_employee_job_assignment primary key(job_assignment_id),
    constraint fk_employee_job_assignment_employee_id foreign key(employee_id) references employee(employee_id),
    constraint fk_employee_job_assignment_section_id foreign key(section_id) references section(section_id)
);

CREATE  table budget_category(
    budget_category_code char(5),
    budget_category_description char(100),
    
    constraint pk_budget_category primary key(budget_category_code)
);

CREATE table budget_charge( 
    transaction_id int not null,
    budget_category_code char(5),
    budget_id int not null,
    transaction_date date,
    transaction_description varchar(100),
    transaction_type char(5),
    
    constraint pk_budget_charge_transaction_id primary key(transaction_id),
    constraint fk_budget_charge_budget_category_code foreign key(budget_category_code) references budget_category(budget_category_code),
    constraint fk_budget_charge_budget_id foreign key(budget_id) references budget(budget_id)
);

CREATE table office(
    office_id int not null,
    office_type char(1),
    office_building char(5),
    office_location varchar(20),
    
    constraint pk_office primary key(office_id)
);

CREATE table employee_office(
    office_id int not null,
    employee_id int not null,
    
    constraint pk_employee_office primary key(office_id, employee_id),
    constraint fk_employee_office_office_id foreign key(office_id) references office(office_id),
    constraint fk_employee_office_employee_id foreign key(employee_id) references employee(employee_id)
);

CREATE table project_employee(
    employee_id int not null,
    project_id int not null,
    project_role varchar(25),
    
    constraint pk_project_employee primary key(employee_id, project_id),
    constraint fk_project_employee_employee_id foreign key(employee_id) references employee(employee_id),
    constraint fk_project_employee_project_id foreign key(project_id) references project(project_id)
);

CREATE table purchase_expense_charge(
    transaction_id int not null,
    purchase_expense_dollars numeric(8,2),
    
    constraint pk_purchase_expense_charge_transaction_id primary key(transaction_id),
    constraint fk_purchase_expense_charge_transaction_id foreign key(transaction_id) references budget_charge(transaction_id)
);

CREATE table hours_charge(
    transaction_id int not null,
    employee_id int not null,
    hours_charged numeric(7,2),
    time_period_begin_date date,
    time_period_end_date date,
    
    constraint pk_hours_charge_transaction_id primary key(transaction_id),
    constraint fk_hours_charge_transaction_id foreign key(transaction_id) references budget_charge(transaction_id),
    constraint fk_hours_charge_employee_id foreign key(employee_id) references employee(employee_id)
);

CREATE table budget_line_item(
    budget_category_code char(5),
    budget_id int not null,
    line_item_budget_dollars numeric(8,2),
    
    constraint pk_budget_line_item primary key(budget_category_code, budget_id),
    constraint fk_budget_line_item_budget_category_code foreign key(budget_category_code) references budget_category(budget_category_code),
    constraint fk_budget_line_item_budget_id foreign key(budget_id) references budget(budget_id)
);

CREATE table travel_expense_charge(
    transaction_id  int not null,
    employee_id int not null,
    trip_expense_dollars NUMERIC(8,2),
    trip_destination VARCHAR(100),
    trip_begin_date date,
    trip_end_date date,

    constraint pk_travel_expense_charge_transaction_id primary key (transaction_id),
    constraint fk_travel_expense_charge_transaction_id foreign key (transaction_id) references budget_charge(transaction_id),
    constraint fk_travel_expense_charge_employee_id foreign key (employee_id) references employee(employee_id)
);

CREATE table committee_member(  
    committee_id int not null,
    employee_id int not null,
    committee_role varchar(25),
    
    constraint pk_committee_member primary key(committee_id, employee_id),
    constraint fk_committee_member_committee_id foreign key(committee_id) references committee(committee_id),
    constraint fk_committee_member_employee_id foreign key(employee_id) references employee(employee_id)
);

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