Why do my CREATE TABLES work alone but not together?

Question

I am trying to run a script with multiple CREATE TABLE statements in MS Access.

When I try to run the script all together I get a CREATE TABLE syntax error on the 2nd CREATE TABLE, but when I run each CREATE TABLE on its own through a SQL validator they pass successfully.

What am I doing wrong by lumping all the CREATE TABLEs into one script?

CREATE TABLE MODEL (
  ModelNum INT NOT NULL,
  Capacity INT NOT NULL,
  MaxRange INT NOT NULL,
  CONSTRAINT ModelPK PRIMARY KEY(ModelNum)
);


CREATE TABLE AIRPLANE (
  RegNum INT NOT NULL,
  ModelNum INT NOT NULL,
  CONSTRAINT AirplanePK PRIMARY KEY(RegNum),
  CONSTRAINT AirplaneFK FOREIGN KEY(ModelNum) REFERENCES MODEL(ModelNum)
);


CREATE TABLE EMPLOYEE (
  SSN INT NOT NULL,
  EmpName VARCHAR(100) NOT NULL,
  Phone INT NULL,
  UnionMemberNum INT NULL,
  CONSTRAINT EmployeePK PRIMARY KEY(SSN)
);


CREATE TABLE TECHNICIAN (
  SSN INT NOT NULL,
  Salary INT NOT NULL,
  CONSTRAINT TechnicianPK PRIMARY KEY(SSN),
  CONSTRAINT TechnicianFK FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN)
);


CREATE TABLE TRAFFICCONTROLLER (
  SSN INT NOT NULL,
  DateOfTraining DATE NULL,
  CONSTRAINT TrafficControllerPK1 PRIMARY KEY(SSN),
  CONSTRAINT TrafficControllerFK1 FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN)
);


CREATE TABLE EXPERT(
  SSN INT NOT NULL,
  ModelNum INT NOT NULL,
  CONSTRAINT ExpertPK1 PRIMARY KEY(SSN),
  CONSTRAINT ExpertFK1 FOREIGN KEY(ModelNum) REFERENCES AIRPLANE(ModelNum)
);


CREATE TABLE TEST(
  Test_Num INT NOT NULL,
  TestName VARCHAR(100) NOT NULL,
  MaxScore INT NOT NULL,
  CONSTRAINT TestPK PRIMARY KEY(Test_Num)
);

CREATE TABLE TESTEVENTS(
  RegNum INT NOT NULL,
  Test_Num INT NOT NULL,
  SSN INT NOT NULL,
  TestDate DATE NOT NULL,
  Score INT NULL,
  CONSTRAINT TestEventsPK1 PRIMARY KEY(RegNum),
  CONSTRAINT TestEventsFK1 FOREIGN KEY(RegNum) REFERENCES AIRPLANE(RegNum),
  CONSTRAINT TestEventsPK2 PRIMARY KEY(Test_Num),
  CONSTRAINT TestEventsFK2 FOREIGN KEY(Test_Num) REFERENCES TEST(Test_Num),
  CONSTRAINT TestEventsPK3 PRIMARY KEY(SSN),
  CONSTRAINT TestEventsFK3 FOREIGN KEY(SSN) REFERENCES TECHNICIAN(SSN),
  CONSTRAINT TestEventsPK4 PRIMARY KEY(TestDate)
);

`sql-server`, `ms-access`, and `mysql` are all different database systems. — Uueerdo, Feb 19 '19 at 17:57
What is the actual error? When you run multiple DDL statements like this the sql compiler parses(goes over to check if its valid). The Airplane create is expecting trafficcontroller to exist. — Chris Wedgwood, Feb 19 '19 at 18:04
Using MS Access the error is just 'Syntax Error in CREATE TABLE statement' . Starting at CREATE TABLE AIRPLANE () but if I replace it with CREATE TABLE EMPLOYEE() it does the same. — sircrisp, Feb 19 '19 at 18:10
Not sure what you mean by "SQL validator" in the question text, but MS Access only supports one SQL statement at a time. There is no way to get Access to automatically execute multiple SQL statements in the same string. As already pointed out, the other tags are not relevant to MS Access. — C Perkins, Feb 19 '19 at 18:29

score 0 · Accepted Answer · answered Feb 19 '19 at 18:34

0

MS Access supports only one statement at a time.

answered Feb 19 '19 at 18:34

sircrisp

1,037
4
17
32

Why do my CREATE TABLES work alone but not together?

1 Answers1