Question

oracle建表时标识符无效

create table shangPin
(
sp_id number(9) constraint shangPin_sp_id_aa primary key,
sp_name varchar2(20) constraint shangPin_sp_name_bb not null,
sp_jiage number(38) not null,
sp_number number(38) not null,
sp_leibie varchar2(10),
constraint shangPin_gys_id_ab FOREIGN KEY(gys_id) references gongYingshang(gys_id)
);

ORA-00904："GYS-ID":标识符无效
之前我建了个
create table gongYingshang
(
gys_id number(9) constraint gongYingshang_gys_id_aa primary key,
gys_name varchar2(20) constraint gongYingshang_gys_name_bb not null,
gys_dizhi varchar2(50),
gys_dianhua number(11)
);

这个表

Answer 1

foreign key(gys_id),此次错误，挂号中应该是shangpin的字段，但shangpin表中没有gys_id

Answer 2

...... 外键约束 是foreign key(本表字段) references 外表(外表的字段)

Answer 3

测试通过
DROP TABLE "SHANGPIN";

CREATE TABLE "SHANGPIN"
("SP_ID" NUMBER(9,0) NOT NULL,
"SP_NAME" VARCHAR2(20) NOT NULL,
"SP_JIAGE" NUMBER(38,0) NOT NULL,
"SP_NUMBER" NUMBER(38,0) NOT NULL,
"SP_LEIBIE" VARCHAR2(10)) ;
ALTER TABLE "SHANGPIN" ADD ( CONSTRAINT SHANGPIN_SP_ID_AA PRIMARY KEY ( "SP_ID" )) ;
ALTER TABLE "SHANGPIN" ADD ( CONSTRAINT gys_id FOREIGN KEY ( "SP_ID" ) REFERENCES "GONGYINGSHANG" ) ;
CREATE UNIQUE INDEX "SHANGPIN_SP_ID_AA" ON "SHANGPIN" ("SP_ID" ) ;
COMMIT WORK ;

Answer 4

外键名要和另外张表一致