SQL database primary key auto-increment settings

Primary key auto-increment

For MySQL, you only need to define the primary key as AUTO_INCREMENT, for example:

The code is as follows:	Copy code
Create table user ( Id int AUTO_INCREMENT, Name varchar (30) not null, Password varchar (30) not null, Primary key (id) ) Insert into user (name, password) VALUES ('sunyzc', '123 ');

The auto-increment of the primary key of SQLServer is also very easy. You only need to define the primary key as IDENTITY, for example:

1. Create a new data table with the field id. Set the id as the primary key.

The code is as follows:	Copy code
Create table tb (id int, constraint pkid primary key (id )) Create table tb (id int primary key)

2. Create a new data table with the field id. Set the id as the primary key and the id is automatically numbered.

The code is as follows:	Copy code
Create table tb (id int identity (1, 1), constraint pkid primary key (id )) Create table tb (id int identity (1, 1) primary key)

3. A data table has been created with the Field id, which sets the id as the primary key.

The code is as follows:	Copy code
Alter table tb alter column id int not null Alter table tb add constraint pkid primary key (id)

4. Delete the primary key

The code is as follows:	Copy code
Declare @ Pk varChar (100 ); Select @ Pk = Name from sysobjects where Parent_Obj = OBJECT_ID ('TB') and xtype = 'PK '; If @ Pk is not null Exec ('alter table tb drop' + @ Pk)

 

The auto-increment of the Oracle primary key requires the custom SEQUENCE:

Create a user table first:

The code is as follows:	Copy code
Create table user ( Id int primary key, Name varchar (30) not null, Password varchar (30) NOT NULL )

Create SEUQENCE:

The code is as follows:	Copy code
Create sequence seq_user increment by 1 start with 1 Nomaxvalue nocycle nocache;

The preceding SQL statement completes the establishment of a SEQUENCE named seq_user. The range is from 1 to infinitely large (determined by your machine ),

Increment by 1, 1 is added for each seq;
Start with 1, seq starts counting from 1;
NOMAXVALUE and seq do not set the maximum value;
NOCYCLE and seq increase continuously without loops. If you set the maximum value, you can use CYCLE to enable sequence to reach the maximum and then loop;
NOCACHE, no buffer. If you provide a CACHE value (such as CACHE 10), the system will automatically read the seq of your CACHE value, which will speed up the operation during repeated operations, however, in the event of an unexpected situation such as downtime or Oracle death, the seq value Retrieved next time will be inconsistent with the previous one.

To insert a new record to the user table, you must use the nextval function (this function retrieves the next value from the seq_user sequence). I can insert data like this:

The code is as follows:	Copy code
Insert into user (id, name, password) VALUES (seq_user.nextval, 'sunyzc', '123 ');

The preceding SQL statement inserts a new record in the user table. The id value is the next number from the seq_user sequence.

 

You can also create a TRIGGER to automatically increase the primary key during insertion:

The code is as follows:	Copy code
Create trigger name BEFORE Insert on table name for each row when (new. Table auto-increment id is null) BEGIN SELECT sequence name. nextval into: new. Table auto-increment id from dual; END;

Then insert the data directly:

The code is as follows:	Copy code
Insert into user (name, password) VALUES ('sunyzc', '123 ');

View all user sequences (logon must be performed as an administrator and the username must be capitalized)

The code is as follows:	Copy code
Select * from dba_sequences where sequence_owner = 'username ';