Oracle-如何创建具有ID的自动递增唯一键的表 [英] Oracle - How do I create a table that has an autoincrementing unique key for the ID
本文介绍了Oracle-如何创建具有ID的自动递增唯一键的表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我第一次使用oracle SQL,但在创建具有唯一键的表时遇到了问题.
This is the first time that I've use oracle SQL and I'm having a problem creating tables with a unique key.
我不明白为什么此自动递增ID无法正常工作:
I don't understand why this auto-incrementing id is not working:
ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
我的下一个问题是为什么我的每条陈述都出现错误:
The next question I have is why I am getting an error in each of my statements:
ORA-00922: missing or invalid option
这是我的代码:
--
-- Sequence for aout incrment
--
CREATE SEQUENCE IF NOT EXISTS AUTO_INC_SEQ
START WITH 1
INCREMENT BY 1;
--
-- Table Person
--
CREATE TABLE IF NOT EXISTS FITNESS_PERSON
(
ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR NOT NULL,
LAST_NAME VARCHAR NOT NULL,
NICK_NAME VARCHAR NOT NULL,
DATE_BIRTH DATE NOT NULL,
PASSWORD VARCHAR NOT NULL,
CONSTRAINT UNIQUE(NICK_NAME)
);
--
-- Table BMR
--
CREATE TABLE IF NOT EXISTS FITNESS_BMR
(
ID NUMBER NOT NULL PRIMARY KEY,
VALUE FLOAT NOT NULL,
VALUE_DATE DATE NOT NULL
);
--
-- M:N for BMR and Person
--
CREATE TABLE IF NOT EXISTS FITNESS_BMR_PERSON
(
BMR_ID NUMBER NOT NULL,
PERSON_ID NUMBER NOT NULL,
FOREIGN KEY(BMR_ID) REFERENCES FITNESS_BMR(ID),
FOREIGN KEY(PERSON_ID) REFERENCES FITNESS_PERSON(ID),
CONSTRAINT BMR_PER PRIMARY KEY(BMR_ID, PERSON_ID)
);
执行此操作的正确方法是什么(创建表并使用唯一的自动递增键).
What's the right way to do this (create a table and with an auto-incrementing key that is unique).
推荐答案
您可以使用表,序列生成唯一ID值和触发器.
You can use a table, a sequence to generate unique ID values and a trigger.
例如:
表格:
CREATE Table FITNESS_BMR
(
ID NUMBER NOT NULL PRIMARY KEY,
VALUE FLOAT NOT NULL,
VALUE_DATE DATE NOT NULL
);
序列:create sequence t1_seq start with 1 increment by 1 nomaxvalue;
触发器:
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON FITNESS_BMR
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT t1_seq.nextval INTO :NEW.ID FROM dual;
END;
/
这篇关于Oracle-如何创建具有ID的自动递增唯一键的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文