在sql server中创建表时出现错误(关键字'PRIMARY'附近的语法不正确)
问题描述:
我想在sql server中创建几个表,但是我得到了下面的错误。你能帮助我吗?在sql server中创建表时出现错误(关键字'PRIMARY'附近的语法不正确)
CREATE TABLE AIRPORT
(
Airport_code NVARCHAR(10) PRIMARY KEY,
Name NVARCHAR(25) NOT NULL,
City NVARCHAR(25) NOT NULL,
State NVARCHAR(25) NOT NULL
);
CREATE TABLE FLIGHT
(
Flight_number NVARCHAR(15) PRIMARY KEY,
Airline NVARCHAR(25) NOT NULL,
Weekdays INTEGER DEFAULT 0
);
CREATE TABLE FLIGHT_LEG
(
Flight_number NVARCHAR(15) NOT NULL,
Leg_number INTEGER NOT NULL,
Departure_airport_code NVARCHAR(10) NOT NULL,
Scheduled_departure_time TIME NOT NULL,
Arrival_airport_code NVARCHAR(10) NOT NULL,
Scheduled_arrival_time TIME NOT NULL,
CONSTRAINT PRIMARY KEY
Pk_Flight_Leg (Flight_number, Leg_number),
CONSTRAINT FOREIGN KEY
Fk_Flight_Leg_Flight (Flight_number) REFERENCES FLIGHT (Flight_number)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE FARE
(
Flight_number NVARCHAR(15) NOT NULL,
Fare_code NVARCHAR(15) NOT NULL,
Amount DECIMAL(10,2) NOT NULL,
Restrictions NVARCHAR(50),
CONSTRAINT PRIMARY KEY
Pk_Fare (Flight_number, Fare_code),
CONSTRAINT FOREIGN KEY
Fk_Fare_Flight (Flight_number) REFERENCES FLIGHT (Flight_number)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE AIRPLANE_TYPE
(
Airplane_type_name NVARCHAR(25) PRIMARY KEY,
Max_seats INTEGER NOT NULL,
Company NVARCHAR(25)
);
CREATE TABLE AIRPLANE
(
Airplane_id NVARCHAR(25) PRIMARY KEY,
Total_number_of_seats INTEGER NOT NULL,
Airplane_type NVARCHAR(25) NOT NULL,
CONSTRAINT FOREIGN KEY
Fk_Airplane_Airplane_Type (Airplane_type) REFERENCES AIRPLANE_TYPE (Airplane_type_name)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE LEG_INSTANCE
(
Flight_number NVARCHAR(15) NOT NULL,
Leg_number INTEGER NOT NULL,
Leg_instance_date Date NOT NULL,
Number_of_available_seats INTEGER,
Airplane_id NVARCHAR(25) NOT NULL,
Departure_airport_code NVARCHAR(10) NOT NULL,
Departure_time TIME NOT NULL,
Arrival_airport_code NVARCHAR(10) NOT NULL,
Arrival_time TIME NOT NULL,
CONSTRAINT PRIMARY KEY
Pk_Leg_Instance (Flight_number, Leg_number, Leg_instance_date),
CONSTRAINT FOREIGN KEY
Fk_Leg_Instance_Flight_Leg (Flight_number, Leg_number)
REFERENCES FLIGHT_LEG (Flight_number, Leg_number)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FOREIGN KEY
Fk_Leg_Instance_Airplane (Airplane_id)
REFERENCES AIRPLANE (Airplane_id)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE CAN_LAND
(
Airplane_type_name NVARCHAR(25) NOT NULL,
Airport_code NVARCHAR(10) NOT NULL,
CONSTRAINT PRIMARY KEY
Pk_Can_Land (Airplane_type_name, Airport_code),
CONSTRAINT FOREIGN KEY
Fk_Can_Land_Airplane_Type (Airplane_type_name)
REFERENCES AIRPLANE_TYPE (Airplane_type_name)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FOREIGN KEY
Fk_Can_Land_Airport (Airport_code)
REFERENCES AIRPORT (Airport_code)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE SEAT_RESERVATION
(
Flight_number NVARCHAR(15) NOT NULL,
Leg_number INTEGER NOT NULL,
Leg_instance_date Date NOT NULL,
Seat_number INTEGER NOT NULL,
Customer_name NVARCHAR(50) NOT NULL,
Customer_phone NVARCHAR(20),
CONSTRAINT PRIMARY KEY
Pk_Seat_Reservation (Flight_number, Leg_number, Leg_instance_date, Seat_number),
CONSTRAINT FOREIGN KEY
Fk_Seat_Reservation_Leg_Instance (Flight_number, Leg_number, Leg_instance_date)
REFERENCES LEG_INSTANCE (Flight_number, Leg_number, Leg_instance_date)
);
我得到这些错误:
消息156,级别15,状态1,行关键字近23
不正确的语法 '主要'。Msg 156,Level 15,State 1,Line 37
关键字'PRIMARY'附近的语法不正确。Msg 156,Level 15,State 1,Line 56
关键字'FOREIGN'附近的语法不正确。Msg 156,Level 15,State 1,Line 73
关键字'PRIMARY'附近的语法不正确。Msg 156,Level 15,State 1,Line 90
关键字'PRIMARY'附近的语法不正确。Msg 156,Level 15,State 1,Line 111
关键字'PRIMARY'附近的语法不正确。
答
您必须命名CONSTRAINT首先你写CONSTRAINT
关键字刚过:
CREATE TABLE AIRPORT (
Airport_code NVARCHAR(10) PRIMARY KEY,
Name NVARCHAR(25) NOT NULL,
City NVARCHAR(25) NOT NULL,
State NVARCHAR(25) NOT NULL
);
CREATE TABLE FLIGHT (
Flight_number NVARCHAR(15) PRIMARY KEY,
Airline NVARCHAR(25) NOT NULL,
Weekdays INTEGER DEFAULT 0
);
CREATE TABLE FLIGHT_LEG (
Flight_number NVARCHAR(15) NOT NULL,
Leg_number INTEGER NOT NULL,
Departure_airport_code NVARCHAR(10) NOT NULL,
Scheduled_departure_time TIME NOT NULL,
Arrival_airport_code NVARCHAR(10) NOT NULL,
Scheduled_arrival_time TIME NOT NULL,
CONSTRAINT Pk_Flight_Leg PRIMARY KEY (Flight_number, Leg_number),
CONSTRAINT Fk_Flight_Leg_Flight FOREIGN KEY (Flight_number)
REFERENCES
FLIGHT (Flight_number)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE FARE (
Flight_number NVARCHAR(15) NOT NULL,
Fare_code NVARCHAR(15) NOT NULL,
Amount DECIMAL(10,2) NOT NULL,
Restrictions NVARCHAR(50),
CONSTRAINT Pk_Fare PRIMARY KEY
(Flight_number, Fare_code),
CONSTRAINT Fk_Fare_Flight FOREIGN KEY
(Flight_number)
REFERENCES
FLIGHT (Flight_number)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE AIRPLANE_TYPE (
Airplane_type_name NVARCHAR(25) PRIMARY KEY,
Max_seats INTEGER NOT NULL,
Company NVARCHAR(25)
);
CREATE TABLE AIRPLANE (
Airplane_id NVARCHAR(25) PRIMARY KEY,
Total_number_of_seats INTEGER NOT NULL,
Airplane_type NVARCHAR(25) NOT NULL,
CONSTRAINT Fk_Airplane_Airplane_Type FOREIGN KEY
(Airplane_type)
REFERENCES
AIRPLANE_TYPE (Airplane_type_name)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE LEG_INSTANCE (
Flight_number NVARCHAR(15) NOT NULL,
Leg_number INTEGER NOT NULL,
Leg_instance_date Date NOT NULL,
Number_of_available_seats INTEGER,
Airplane_id NVARCHAR(25) NOT NULL,
Departure_airport_code NVARCHAR(10) NOT NULL,
Departure_time TIME NOT NULL,
Arrival_airport_code NVARCHAR(10) NOT NULL,
Arrival_time TIME NOT NULL,
CONSTRAINT Pk_Leg_Instance PRIMARY KEY
(Flight_number, Leg_number, Leg_instance_date),
CONSTRAINT Fk_Leg_Instance_Flight_Leg FOREIGN KEY
(Flight_number, Leg_number)
REFERENCES
FLIGHT_LEG (Flight_number, Leg_number)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT Fk_Leg_Instance_Airplane FOREIGN KEY
(Airplane_id)
REFERENCES
AIRPLANE (Airplane_id)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE CAN_LAND (
Airplane_type_name NVARCHAR(25) NOT NULL,
Airport_code NVARCHAR(10) NOT NULL,
CONSTRAINT Pk_Can_Land PRIMARY KEY
(Airplane_type_name, Airport_code),
CONSTRAINT Fk_Can_Land_Airplane_Type FOREIGN KEY
(Airplane_type_name)
REFERENCES
AIRPLANE_TYPE (Airplane_type_name)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT Fk_Can_Land_Airport FOREIGN KEY
(Airport_code)
REFERENCES
AIRPORT (Airport_code)
ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE SEAT_RESERVATION (
Flight_number NVARCHAR(15) NOT NULL,
Leg_number INTEGER NOT NULL,
Leg_instance_date Date NOT NULL,
Seat_number INTEGER NOT NULL,
Customer_name NVARCHAR(50) NOT NULL,
Customer_phone NVARCHAR(20),
CONSTRAINT Pk_Seat_Reservation PRIMARY KEY
(Flight_number, Leg_number, Leg_instance_date, Seat_number),
CONSTRAINT Fk_Seat_Reservation_Leg_Instance FOREIGN KEY
(Flight_number, Leg_number, Leg_instance_date)
REFERENCES
LEG_INSTANCE (Flight_number, Leg_number, Leg_instance_date)
);
答
你必须在代码中的两个问题。
- 当您想命名约束时,使用
CONSTRAINT
关键字。 - 列出主键和外键列时不要重复表名。
例如,对于FLIGHT_LEG
,定义应该是这样的:
CREATE TABLE FLIGHT_LEG (
Flight_number NVARCHAR(15) NOT NULL,
Leg_number INTEGER NOT NULL,
Departure_airport_code NVARCHAR(10) NOT NULL,
Scheduled_departure_time TIME NOT NULL,
Arrival_airport_code NVARCHAR(10) NOT NULL,
Scheduled_arrival_time TIME NOT NULL,
PRIMARY KEY (Flight_number, Leg_number),
FOREIGN KEY (Flight_number) REFERENCES FLIGHT (Flight_number)
ON UPDATE CASCADE ON DELETE CASCADE
);
我会鼓励你想想合成键表格 - 数字标识列,而不是字符串。数字键在索引和外键关系中效率更高。
您应该首先命名FK约束 – Sami
例如'CONSTRAINT Pk_Flight_Leg PRIMARY KEY(Flight_number,Leg_number),' – Sami
okey nice谢谢 – BRKZKN