CREATE TABLE attachments (
id number NOT NULL,
container_id number default '0' NOT NULL,
container_type varchar2(30) default '' NOT NULL,
filename varchar2(255) default '' NOT NULL,
disk_filename varchar2(255) default '' NOT NULL ,
filesize number default '0' NOT NULL,
content_type varchar2(255) default '',
digest varchar2(40) default '' NOT NULL,
downloads number default '0' NOT NULL,
author_id number default '0' NOT NULL ,
created_on date default NULL,
description varchar2(255) default NULL,
PRIMARY KEY (id),
KEY index_attachments_on_container_id_and_container_type (container_id,container_type),
KEY index_attachments_on_author_id (author_id),
KEY index_attachments_on_created_on (created_on)
)
这个是从Mysql改到Oracle的一个建表的问题
出错的提示:ORA-00972: identifier is too long
这个
id number NOT NULL,
container_id number default '0' NOT NULL,
container_type varchar2(30) default '' NOT NULL,
filename varchar2(255) default '' NOT NULL,
disk_filename varchar2(255) default '' NOT NULL ,
filesize number default '0' NOT NULL,
content_type varchar2(255) default '',
digest varchar2(40) default '' NOT NULL,
downloads number default '0' NOT NULL,
author_id number default '0' NOT NULL ,
created_on date default NULL,
description varchar2(255) default NULL,
PRIMARY KEY (id),
KEY index_attachments_on_container_id_and_container_type (container_id,container_type),
KEY index_attachments_on_author_id (author_id),
KEY index_attachments_on_created_on (created_on)
)
这个是从Mysql改到Oracle的一个建表的问题
出错的提示:ORA-00972: identifier is too long
这个
这个索引名称长度超过30,改小点。
通过desc user_indexes可以查看最大索引长度
id number NOT NULL,
container_id number default '0' NOT NULL,
container_type varchar2(30) default '' NOT NULL,
filename varchar2(255) default '' NOT NULL,
disk_filename varchar2(255) default '' NOT NULL ,
filesize number default '0' NOT NULL,
content_type varchar2(255) default '',
digest varchar2(40) default '' NOT NULL,
downloads number default '0' NOT NULL,
author_id number default '0' NOT NULL ,
created_on date default NULL,
description varchar2(255) default NULL
);
alter table attachments
add constraint PK_ID primary key (id);
create index idx_attachments_on_au_con_type on attachments (container_id,container_type);
create index idx_attachments_on_author_id on attachments (author_id);
create index idx_attachments_on_created_on on attachments (created_on);
`id` int(11) NOT NULL auto_increment,
`commented_type` varchar(30) NOT NULL default '',
`commented_id` int(11) NOT NULL default '0',
author_id NUMBER default '0' NOT NULL ,
comments text,
created_on date NOT NULL,
updated_on date NOT NULL,
PRIMARY KEY (id),
KEY index_comments_on_commented_id_and_commented_type (commented_id,commented_type),
KEY index_comments_on_author_id (author_id)
)
最好建立一个序列,插入数据时获取序列值,序列会自动加1.
------------------------------------------------------
建立一个最小为1,最大为999999999的一个序列号会自动循环的序列
create sequence 序列名
increment by 1
start with 1
maxvalue 999999999
cycle; 当向表中插入数据时,SQL语句写法如下: SQL> insert into 表名 values(序列名.nextval,列1值,列2值...);