Automatic growth of fields in Oracle data tables

Because the MySQL database has been used, today just a whim, thinking and Java are the same boss out of Oracle himself will not, say go will not be a bit embarrassing, so began to turn data, watching video to get up, step by step, think about what to write, start from the creation of the table, OK, It's not a lot to talk about. Create a table:

User table:

CREATE TABLE User (

ID Number (5,0) NOT null primary key auto_increment,

DeptID Number (5,0) is not NULL,

Username VARCHAR2 () not NULL,

Password varchar2 () not NULL,

,,,,,,,,,

Constraint Fk_deptid foreign KEY (deptid) references Department (ID)

);

Department table

CREATE TABLE Department (

ID Number (5,0) NOT null primary key auto_incremnt,

Dept_name varchar2 () Notn ull

)

All the way it's natural, feel good cow b ah, two tables, the code will be knocked out, think this is not the same as MySQL to create a data table, Ha ha,,,,

Compile SQL statement,, how can, how can, unexpectedly error, once again check the code, feel no error ah, then run, or error,,, Oh, my God

Check Baidu, and then found that the original Oracle set autogrow field is not so, completely and MySQL is different, not the same AH

OK, re-create the table:

User table

CREATE TABLE User (

ID Number (5) Not null primary key,

DeptID Number (5) is not NULL,

Username VARCHAR2 () not NULL,

Password varchar2 () not NULL,

,,,,,,

Constraint Fk_deptid foreign KEY (deptid) references Department (ID),

Constraint Un_username unique (username)

);

Department table

CREATE TABLE Department (

ID Number (5) Not null primary key,

Deptname varchar2 () NOT NULL

);

The second step is to create an Oracle sequence User_increment_sequence

Create sequence User_increment_sequence

MinValue 1

MaxValue 999999999

Increment by 1

Start with 1

NoCache

The third step is to create a trigger User_increment_trigger

Create or Replace Trigger User_increment_trigger

Before insert

For each row

Begin

Select Nextval into:new.id from dual;

End

/

Fourth step, test the results to see if the field can be automatically added

Add a piece of data to the user table

Insert into user values (2, ' KKK ', ' KKK ', ' KKK '); ----------here 2 for the field of the foreign key department number

Insert into user values (2, ' ddd ', ' ddd ', ' ddd ');

query, the resulting data shows that the exact field is automatically added, so far, complete

Automatic growth of fields in Oracle data tables