为什么这个SQL代码显示“缺少右括号”的错误?
问题描述:
CREATE TABLE PoolActivity_T
(PoolID NUMBER(11,0) NOT NULL,
ServiceDate DATE DEFAULT SYSDATE NOT NULL,
ActivityID NUMBER(11,0) NOT NULL,
CONSTRAINT PoolActivity_PK PRIMARY KEY (PoolID,ServiceDate,ActivityID)
CONSTRAINT PoolActivity_FK FOREIGN KEY (PoolID)
REFERENCES PoolVisit_T(PoolID),
CONSTRAINT PoolActivity_FK FOREIGN KEY (ActivityID)
REFERENCES Activity_T (ActivityID)
);
这是我的代码,我不知道为什么出现错误。为什么这个SQL代码显示“缺少右括号”的错误?
CREATE TABLE Customer_T
(CustomerID NUMBER(11,0) NOT NULL,
CustomerFname VARCHAR2(25),
CustomerMname VARCHAR2(25),
CustomerLname VARCHAR2(25),
CustomerAddress1 VARCHAR2(50),
CustomerAddress2 VARCHAR2(50),
CustomerCity VARCHAR2(25),
CustomerState CHAR(2),
CustomerZipCode NUMBER(5,0),
CustomerPhoneNumber NUMBER(11,0),
CONSTRAINT Customer_PK PRIMARY KEY (CustomerID)
);
CREATE TABLE Pool_T
(PoolID NUMBER(11,0) NOT NULL,
PoolAddress1 VARCHAR2(25),
PoolAddress2 VARCHAR2(25),
PoolCity VARCHAR2(25),
PoolState VARCHAR2(25),
PoolZipCode NUMBER(5,0),
TypeSurface VARCHAR2(9),
Gallon INTEGER,
Filters VARCHAR2(25),
VisitPerMonth INTEGER,
Dates DATE DEFAULT SYSDATE,
Fee NUMBER(11,0),
CONSTRAINT Pool_PK PRIMARY KEY (PoolID)
);
CREATE TABLE PoolVisit_T
(PoolID NUMBER(11,0) NOT NULL,
ServiceDate DATE DEFAULT SYSDATE NOT NULL,
LengthStay INTEGER,
CONSTRAINT Poolvisit_PK PRIMARY KEY (PoolID,ServiceDate)
CONSTRAINT PoolVisit_FK FOREIGN KEY (PoolID)
REFERENCES Pool_T (PoolID)
);
CREATE TABLE PoolActivity_T
(PoolID NUMBER(11,0) NOT NULL,
ServiceDate DATE DEFAULT SYSDATE NOT NULL,
ActivityID NUMBER(11,0) NOT NULL,
CONSTRAINT PoolActivity_PK PRIMARY KEY (PoolID,ServiceDate,ActivityID)
CONSTRAINT PoolActivity_FK FOREIGN KEY (PoolID)
REFERENCES PoolVisit_T(PoolID),
CONSTRAINT PoolActivity_FK FOREIGN KEY (ActivityID)
REFERENCES Activity_T (ActivityID)
);
CREATE TABLE PoolChemical_T
(PoolID NUMBER(11,0) NOT NULL,
ServiceDate DATE DEFAULT SYSDATE NOT NULL,
ChemicalName VARCHAR2(25) NOT NULL,
Quantity INTEGER,
Cost NUMBER(11,2),
CONSTRAINT PoolChemical_PK PRIMARY KEY (PoolID,ServiceDate,ChemicalName)
CONSTRAINT PoolChemical_FK FOREIGN KEY (PoolID)
REFERENCES PoolVisit_T(PoolID)
CONSTRAINT PoolChemical_FK FOREIGN KEY (ChemicalName)
REFERENCES Chemical_T(ChemicalName)
);
CREATE TABLE Activity_T
(ActivityID NUMBER(11,0) NOT NULL,
ActivityDesc VARCHAR2(500),
CONSTRAINT Activity_PK PRIMARY KEY (ActivityID));
CREATE TABLE Chemical_T
(ChemicalName VARCHAR2(25) NOT NULL,
CONSTRAINT Chemical_PK PRIMARY KEY (ChemicalName));
答
的错误不只是在你突出的表,它也是在人之前,PoolVisit_T
。在这两个,你只是缺少一个逗号主键约束后:
CREATE TABLE PoolVisit_T
(PoolID NUMBER(11,0) NOT NULL,
ServiceDate DATE DEFAULT SYSDATE NOT NULL,
LengthStay INTEGER,
CONSTRAINT Poolvisit_PK PRIMARY KEY (PoolID,ServiceDate), -- was missing comma
CONSTRAINT PoolVisit_FK FOREIGN KEY (PoolID)
REFERENCES Pool_T (PoolID)
);
CREATE TABLE PoolActivity_T
(PoolID NUMBER(11,0) NOT NULL,
ServiceDate DATE DEFAULT SYSDATE NOT NULL,
ActivityID NUMBER(11,0) NOT NULL,
CONSTRAINT PoolActivity_PK PRIMARY KEY (PoolID,ServiceDate,ActivityID), -- was missing
CONSTRAINT PoolActivity_FK FOREIGN KEY (PoolID)
REFERENCES PoolVisit_T(PoolID),
CONSTRAINT PoolActivity_FK FOREIGN KEY (ActivityID)
REFERENCES Activity_T (ActivityID)
);
而你在之后的一个缺二:
CREATE TABLE PoolChemical_T
(PoolID NUMBER(11,0) NOT NULL,
ServiceDate DATE DEFAULT SYSDATE NOT NULL,
ChemicalName VARCHAR2(25) NOT NULL,
Quantity INTEGER,
Cost NUMBER(11,2),
CONSTRAINT PoolChemical_PK PRIMARY KEY (PoolID,ServiceDate,ChemicalName), -- was missing
CONSTRAINT PoolChemical_FK FOREIGN KEY (PoolID)
REFERENCES PoolVisit_T(PoolID), -- was missing
CONSTRAINT PoolChemical_FK FOREIGN KEY (ChemicalName)
REFERENCES Chemical_T(ChemicalName)
);
如果你使用像SQL开发的IDE它将突出显示像这样的语法问题。
但你仍然有问题;您PoolActivity_T
外键:
CONSTRAINT PoolActivity_FK FOREIGN KEY (PoolID)
REFERENCES PoolVisit_T(PoolID),
只指PoolID
,但PoolVisit_T
主键和主键有两列,因此需要为:
CONSTRAINT PoolActivity_FK FOREIGN KEY (PoolID,ServiceDate)
REFERENCES PoolVisit_T(PoolID,ServiceDate),
,你已经使用相同的FK名两个约束,所以下一位是:
CONSTRAINT PoolActivity_FK2 FOREIGN KEY (ActivityID)
REFERENCES Activity_T (ActivityID)
(但挑选更具描述性的名称FK,也许),和您必须在此之前创建Activity_T
表格。
而且类似的问题与PoolChemical_T
:
CONSTRAINT PoolChemical_PK PRIMARY KEY (PoolID,ServiceDate,ChemicalName),
CONSTRAINT PoolChemical_FK FOREIGN KEY (PoolID,ServiceDate)
REFERENCES PoolVisit_T(PoolID,ServiceDate),
CONSTRAINT PoolChemical_FK2 FOREIGN KEY (ChemicalName)
REFERENCES Chemical_T(ChemicalName)
,创造Chemical_T
第一。
SQL Fiddle创建所有表。
不是uml相关的,可能是oracle(不是mysql) – amdixon
我怎么能一次缩进这么多的代码? – MarioHo
@MarioHo - [请参阅帮助中心](http://*.com/help/formatting)。 –