不明白为什么我的 SQL 不起作用 [英] Don't understand why my SQL isn't working
问题描述
/* DROP TABLE STATEMENTS*/
DROP TABLE BUILDING CASCADE CONSTRAINTS PURGE;
DROP TABLE ROOM CASCADE CONSTRAINTS PURGE;
DROP TABLE CONFERENCESESSION CASCADE CONSTRAINTS PURGE;
DROP TABLE SPEAKER CASCADE CONSTRAINTS PURGE;
/* CREATE TABLE STATEMENTS*/
CREATE TABLE BUILDING(
BUILDINGNO CHAR(2),
BUILDINGWING VARCHAR2(15),
BUILDINGLANE VARCHAR2(15),
CONSTRAINT BUILDING_PK PRIMARY KEY(BUILDINGNO));
CREATE TABLE ROOM(
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
ROOMCAPACITY NUMBER(3),
CONSTRAINT ROOM_PK PRIMARY KEY(BUILDINGNO, ROOMNO),
CONSTRAINT ROOM_FK FOREIGN KEY(BUILDINGNO) REFERENCES BUILDING(BUILDINGNO));
CREATE TABLE SPEAKER(
SPEAKERID CHAR(2),
SPEAKERNAME VARCHAR2(20),
SPEAKERADDRESS VARCHAR2(50),
SPEAKERPHONE CHAR(12),
CONSTRAINT SPEAKER_PK PRIMARY KEY(SPEAKERID));
CREATE TABLE CONFERENCESESSION(
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4, 2),
CONSTRAINT CONFERENCE_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCE_FK1 FOREIGN KEY(BUILDINGNO)REFERENCES BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCE_FK3 FOREIGN KEY(SPEAKERID) REFERENCES SPEAKER(SPEAKERID));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
运行上述代码时出现此错误:
I get this error when running the above code:
Table BUILDING 已删除.
Table BUILDING dropped.
Table ROOM 掉了.
Table ROOM dropped.
从第 4 行开始出错 -
删除表会议级联约束清除
错误报告 -
ORA-00942: 表或视图不存在
00942. 00000 - 表或视图不存在"
*原因:
*动作:
Error starting at line : 4 in command -
DROP TABLE CONFERENCESESSION CASCADE CONSTRAINTS PURGE
Error report -
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Table SPEAKER 掉线了.
Table SPEAKER dropped.
表 BUILDING 已创建.
Table BUILDING created.
表室已创建.
表 SPEAKER 已创建.
Table SPEAKER created.
从第 28 行开始出错 -
创建表会议(
会话 ID 字符(4),
建筑无字符(2),
房间字符(2),
扬声器 ID 字符(2),
会议日期,
会话价格编号(4, 2),
CONSTRAINT CONFERENCE_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCE_FK1 FOREIGN KEY(BUILDINGNO)REFERENCES BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCE_FK3 FOREIGN KEY(SPEAKERID) REFERENCES SPEAKER(SPEAKERID))
Error starting at line : 28 in command -
CREATE TABLE CONFERENCESESSION(
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4, 2),
CONSTRAINT CONFERENCE_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCE_FK1 FOREIGN KEY(BUILDINGNO)REFERENCES BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCE_FK3 FOREIGN KEY(SPEAKERID) REFERENCES SPEAKER(SPEAKERID))
错误报告-
ORA-02270: 此列列表没有匹配的唯一键或主键
02270. 00000 - 此列列表没有匹配的唯一键或主键"
*原因:CREATE/ALTER TABLE 语句中的 REFERENCES 子句提供的列列表在被引用表中没有匹配的唯一键或主键约束.
*操作:使用 ALL_CONS_COLUMNS 目录视图查找正确的列名
Error report -
ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view
有什么帮助吗?
推荐答案
CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO)
:外键必须引用整个em> 另一个表的主键(或唯一键).所以只需添加 buildingno:
CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO)
: A foreign key has to reference the entire primary key (or unique key) of another table. So simply add buildingno:
CONSTRAINT CONFERENCE_FK2 FOREIGN KEY(BUILDINGNO, ROOMNO) REFERENCES ROOM(BUILDINGNO, ROOMNO)
这篇关于不明白为什么我的 SQL 不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!