向现有表添加外键 [英] Adding a foreign key to existing table

问题描述

我一直试图将这些键添加到我的表中，但是出现错误

I have been trying to add these keys to my table but I get an error

ORA-01735:无效的ALTER TABLE选项

ORA-01735: invalid ALTER TABLE option

我的代码:

ALTER TABLE Room
ADD FOREIGN KEY (RoomType_ID) REFERENCES RoomType(RoomType_ID), 
ADD FOREIGN KEY (Reservation_ID) REFERENCES Reservation(Reservation_ID), 
ADD FOREIGN KEY (Gust_ID) REFERENCES Gust(Gust_ID);

表格

CREATE TABLE Gust ( Gust_ID INT NOT NULL PRIMARY KEY, First_Name VARCHAR(50), Last_Name VARCHAR(50), Email VARCHAR(20), phone_number INT(10), Address VARCHAR(30) )

CREATE TABLE Reservation ( Reservation_ID INT NOT NULL PRIMARY KEY, Start_Date Date, End_Date Date )

CREATE TABLE Room ( Room_ID INT NOT NULL PRIMARY KEY, Price INT )

CREATE TABLE RoomType ( RoomType_ID INT NOT NULL PRIMARY KEY, Class VARCHAR(10), ExtraPrice INT )

多个 FOREIGN KEYS 的

推荐答案

ALTER 语句不起作用.

每个 CONSTRAINT 应该单独添加:

CREATE TABLE Gust ( Gust_ID INT PRIMARY KEY, First_Name VARCHAR(50), Last_Name VARCHAR(50), Email VARCHAR(20), phone_number INT, Address VARCHAR(30) );
CREATE TABLE Reservation ( Reservation_ID INT PRIMARY KEY, Start_Date Date, End_Date Date );
CREATE TABLE RoomType ( RoomType_ID INT NOT NULL PRIMARY KEY, Class VARCHAR(10), ExtraPrice INT );

CREATE TABLE Room(Room_ID int PRIMARY KEY, Price INT, Reservation_ID int,Gust_ID int );

ALTER TABLE Room ADD FOREIGN KEY (Room_ID) REFERENCES RoomType(RoomType_ID);

ALTER TABLE Room ADD FOREIGN KEY (Reservation_ID) REFERENCES Reservation(Reservation_ID); 
-- the table Room is assumed to have a column Reservation_ID 

ALTER TABLE Room ADD FOREIGN KEY (Gust_ID) REFERENCES Gust(Gust_ID);
    -- the table Room is assumed to have a column Gust_ID

如果不需要系统生成的约束名称，则使用这些；

If System-generated constraint name not to be wanted, then using these ;

ALTER TABLE Room ADD CONSTRAINT fk_RoomType_ID FOREIGN KEY (Room_ID) 
                                               REFERENCES RoomType(RoomType_ID);

ALTER TABLE Room ADD CONSTRAINT fk_Reservation_ID FOREIGN KEY (Reservation_ID) 
                                                REFERENCES Reservation(Reservation_ID); 

ALTER TABLE Room ADD CONSTRAINT fk_Gust_ID FOREIGN KEY (Gust_ID) 
                                           REFERENCES Gust(Gust_ID);

可能是首选.

P.S.:

• 请勿在 PRIMARY KEY 和 PRIMARY KEY 中使用 NOT NULL 包括 NOT NULL .
• 不能为 INT 列定义长度.

• Do Not Use NOT NULL with PRIMARY KEY, PRIMARY KEY already includes NOT NULL.
• The length can not be defined for an INT column.

这篇关于向现有表添加外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！