Database Navicat for Oracle set up uniqueness and incremental sequence experiments

This article focuses on Navicat for Oracle setting uniqueness constraints and setting a field as an ascending sequence. The main text is mainly graphic, although it is a very simple basis of the article, but still hope that you have some help.
Recommended previous post: [Database] Navicat for Oracle basic usage graphic INTRODUCTION

I. Setting the uniqueness

Reference article: Oracle's Uniqueness Constraints (unique Constraint) usage
Uniqueness Constraint English is a unique Constraint, which is a constraint in which a field or multiple fields in a table are joined together to uniquely identify a record. A union field can contain null values.

What's the difference between a single constraint and a primary key?
You might wonder if the primary key (Primary key) is not the only one that identifies a single table?
The primary key is all columns that make up the primary key cannot contain null values, and if the uniqueness constraint consists of multiple columns, some of the columns can contain null values. Also, note that Oracle does not tolerate both creating a primary key and creating a uniqueness constraint on the same column.

For example, the Student table student (ID, stu_id, stu_name, sex, email, year), where the primary key is the student number (STU_ID), assuming that the table students need to input the data one time, so two fields form a primary key.
At the same time, in order to facilitate the traversal, the sequence number ID also needs to be set differently, when the ID is set to uniqueness.

Uniqueness constraints can be created when a table is created or by using the ALTER TABLE statement.
The SQL statement that creates the uniqueness constraint is as follows:

ALTER TABLE table_name  ADD CONSTRAINT constraint_name  

The SQL statement that removes the uniqueness constraint is as follows:

ALTER TABLE table_name  DROP CONSTRAINT constraint_name;  

use the Navicat for Orcale method as follows:

There are two ways to set a Uniqueness constraint:
ALTER TABLE T_USER_INFO add constraint u_id unique (ID);
ALTER TABLE T_user_info add unique (ID);

The view table T_user_info structure looks like this, where the user name is the primary key:

also view uniqueness to see the ordinal ID just set:
when modifying the ID field in table t_user_info, an error occurs when the same value or insert data ID already exists:

two. Set the increment sequence

The insert ordinal ID described earlier, but the number in the database is certainly many, how to insert ID data in the process of more convenient to avoid repeated insertions? This involves setting up a method with an incremented sequence of IDs.
Navicat for Orcale is not like MySQL only needs to tick the increment sequence, it needs the SQL statement to set. So this part of the content is also very important, I hope to help you.

The main steps include:
1) Set the sequence
2) Set the trigger for the corresponding sequence and table
3) Use the corresponding trigger when inserting data
Here's how:

The first section sets the sequence
1. You can manipulate the sequence by clicking the "other =" sequence:

2. Click inside to design a sequence, create a new sequence, and delete a sequence.

3. It is also possible to add a sequence to the code, corresponding to the table t_user_info the user table.

CREATE SEQUENCE user_info_seq  minvalue 1  maxvalue 9999999999999999999  increment by 1  start with 1;  

4. Click on the new sequence USER_INFO_SEQ to discover the specific sequence:

The second part sets the trigger
5. Then set the trigger for the field ID in the corresponding table T_user_info to automatically insert the self-growing sequence before inserting the data into the table.

CREATE OR REPLACE TRIGGER trigger_user    before INSERT on t_user_info for each    row if    (New.id is NULL)  Beg In    select User_info_seq.nextval Into:NEW.ID from dual;    End  

create success as shown, note that the When (New.id is NULL) cannot be absent because the default ID value is added when data is inserted, allowing the trigger to be automatically replenished.


6. View the table T_user_info structure to see the corresponding trigger:
However, it is possible to report a compilation error [ERR] ora-24344:success with compilation error. The reason is that all column names in the table need to be uppercase, or you can manually add "update Field", otherwise, although there is an increase in the trigger, but the update field is empty, so the insertion is not successful, the insertion will not trigger the primary key sequence.

The third part inserts the data
7. Inserting data

Insert into T_user_info (Username,pwd,status,dw_name,dw_code) VALUES (' Eastmount ', ' 000000 ', ' 2 ', ' Software Academy ', ' 08111003 ');

You can insert data at this point, where the field ID is added automatically.

if error [ERR] ora-00001:unique constraint (Guicai. SYS_C0017014) violated as shown in. This may be because a unique ID is set in the table, and there is a phenomenon of duplicate data. If you cannot set a uniqueness constraint and need to remain unique, we recommend using the Sys_guid () function to produce a unique encoding. In fact, the idea is to get the maximum ID value in the table and automatically add 1.


Reference:Oracle implements SQL Server-like column self-growth capabilities-JSSG_TZW
          Add an AutoNumber sequence to Oracle-what's left
          How the DB design-time ID uniqueness (or self-increment) in Oracle is set


Finally, I hope that the article is helpful to you, this article is mainly about the use of navicat for Orcale software, including the setting of unique and incremental sequence of experiments, mainly through the form of graphics and text, perhaps this very simple problem is complicated, But for beginners still have a certain help, master do not spray ~
Recently really good busy, yesterday slept for 4 hours, and now stay up to five o'clock in the morning, but often share a blog post, feel very excited or happy, some things can not say, happy good!
(By:eastmount 2016-04-11 5 o'clock in the morninghttp://blog.csdn.net/eastmount/)

Database Navicat for Oracle set up uniqueness and incremental sequence experiments