我用SqlServer 建立是这样的
create table student(
id int identity(1,1) primary key,
studentName varchar(20),
age int
)
create table scores（
id int identity(1,1) primary key,
studentId int refereces student(id),
score int
）
我现在想用Oracle来建立这两张表
请问这么建立啊
建表转化为Oracle语句
[code="java"]create table student(
id NUMBER(10,0) not null,
studentName VARCHAR2,
age NUMBER(10,0),
constraint pk_student primary key(id))
) ;
create table scores（
id NUMBER(10,0) identity(1,1) not null,
studentId NUMBER(10,0)
score NUMBER(10,0),
constraint pk_scores primary key(id))
） [/code]
[color=darkblue][size=medium]refereces student(id)转化[/size][/color][code="java"]alter table scores
add constraint fk_student_scores foreign key(studentId) references student(id);[/code]
identity(1,1)
[color=blue][size=medium]创建自动增长序列[/size][/color]
[code="java"]CREATE SEQUENCE Increase_Sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;[/code]
[color=darkblue][size=medium]创建触发器[/size][/color]
[code="java"]CREATE TRIGGER scores BEFORE
insert ON scores FOR EACH ROW
begin
select Increase_Sequence.nextval into:New.userid from dual;
end;
CREATE TRIGGER table BEFORE
insert ON table FOR EACH ROW
begin
select Increase_Sequence.nextval into:New.userid from dual;
end;[/code]
[color=darkblue][size=small]
参考资料如下：[/size][/color]
MySQL 外键的设置及作用：
http://www.jianglb.com/2007/09/02/mysql-foreign-key.html
alter 添加外键(oracle)：
http://blog.sina.com.cn/s/blog_65b73fb70100h6qa.html
MYSQL 与 Oracle 之间的数据类型转换：
http://brianyuzhao.iteye.com/blog/429774
oracle主键自增：
http://www.cnblogs.com/abcdwxc/archive/2009/02/13/1390120.html
[code="sql"]CREATE TABLE STUDENT
( ID INT PRIMARY KEY,
STUDENTNAME VARCHAR(20),
AGE INT
);
CREATE TABLE SCORES
(
ID INT PRIMARY KEY,
STUDENTID INT,
SCORE INT,
CONSTRAINT "SCORESA_STUDENTA_FK1" FOREIGN KEY ("STUDENTID") REFERENCES "STUDENT" ("ID") ENABLE
);
CREATE SEQUENCE STUDENT_SEQ_TABLE MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE SCORES_SEQ_TABLE MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE OR REPLACE TRIGGER STUDENT_TRG_TABLE BEFORE
INSERT ON STUDENT FOR EACH ROW BEGIN
SELECT STUDENT_SEQ_TABLE.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
CREATE OR REPLACE TRIGGER SCORES_TRG_TABLE BEFORE
INSERT ON SCORES FOR EACH ROW BEGIN
SELECT SCORES_SEQ_TABLE.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
INSERT INTO STUDENT
(STUDENTNAME,AGE
) VALUES
(2,2
);
INSERT INTO STUDENT VALUES
(STUDENT_SEQ_TABLE.NEXTVAL,2,2
);[/code]
由于ORACLE 没有像 MSSQL的identity(1,1) 自动增长
因此需要特殊处理，这里使用的是序列，并使用了触发器，来解决ORACLE主键的自动增长问题
地INSERT时 可以
INSERT INTO STUDENT
(STUDENTNAME,AGE
) VALUES
(2,2
);
触发器会自动执行主键的附值
也可以这样操作
INSERT INTO STUDENT VALUES
(STUDENT_SEQ_TABLE.NEXTVAL,2,2
);