开发者

sql foreign key error

i want to create these two tables but i'm getting an error which says SECTION keys that being referenced by foreign keys of HOLD are not primary key. as it's seen in the query they are primary keys. how can i solve this problem?

create table SECTION( 
ID integer foreign key references TERM(ID),
CID integer for开发者_开发技巧eign key references COURSE(CID),
SECT integer,
constraint PK_SECTION primary key (ID,CID,SECT),
);

create table HOLD( 
NAME varchar(30) foreign key references INSTRUCTOR(NAME),
ID integer foreign key references SECTION(ID),
CID integer foreign key references SECTION(CID),
SECT integer foreign key references SECTION(SECT),
constraint PK_HOLD primary key (NAME,ID,CID,SECT),
);


ID,CID,SECT are not primary keys, they all together are one primary key (of multiple attributes). You have to reference them as one like this:

CONSTRAINT "HOLD_FK01" FOREIGN KEY ("ID", "CID", "SECT")
      REFERENCES "SECTION" ("ID", "CID", "SECT")


By using this statement constraint PK_SECTION primary key (ID,CID,SECT) you are making only on primary key and by the following statement ID integer foreign key references TERM(ID), CID integer foreign key references COURSE(CID) you are trying to make two foreign keys but the thing is ID and CID are not primary key there combination is a primary key(ID,CID,SECT)

To rectify this you need to create Individual primary key ID and CID

create table SECTION( 
ID integer foreign key references TERM(ID),
CID integer foreign key references COURSE(CID),
SECT integer,
constraint PK_SECTION_ID primary key (ID),
constraint PK_SECTION_SECT primary key (SECT),
constraint PK_SECTION_CID primary key (CID)
);

Or

CONSTRAINT "FK" FOREIGN KEY ("ID", "CID") REFERENCES "SECTION" ("ID", "CID")

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜