![]() 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. However, it is possible to report a compilation error ora-24344:success with compilation error. View the table T_user_info structure to see the corresponding trigger: 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.ĬREATE 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.Ħ. Click on the new sequence USER_INFO_SEQ to discover the specific sequence:ĥ. It is also possible to add a sequence to the code, corresponding to the table t_user_info the user table.ĬREATE SEQUENCE user_info_seq minvalue 1 maxvalue 9999999999999999999 increment by 1 start with 1 Ĥ. Click inside to design a sequence, create a new sequence, and delete a sequence.ģ. You can manipulate the sequence by clicking the "other =" sequence:Ģ. ![]() So this part of the content is also very important, I hope to help you.Ģ) Set the trigger for the corresponding sequence and tableģ) Use the corresponding trigger when inserting dataġ. Navicat for Orcale is not like MySQL only needs to tick the increment sequence, it needs the SQL statement to set. 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. When modifying the ID field in table t_user_info, an error occurs when the same value or insert data ID already exists: ![]() The view table T_user_info structure looks like this, where the user name is the primary key:Īlso view uniqueness to see the ordinal ID just set: There are two ways to set a Uniqueness constraint:ĪLTER TABLE T_USER_INFO add constraint u_id unique (ID) The SQL statement that creates the uniqueness constraint is as follows:ĪLTER TABLE table_name ADD CONSTRAINT constraint_name The SQL statement that removes the uniqueness constraint is as follows:ĪLTER TABLE table_name DROP CONSTRAINT constraint_name use the Navicat for Orcale method as follows: Uniqueness constraints can be created when a table is created or by using the ALTER TABLE statement. Also, note that Oracle does not tolerate both creating a primary key and creating a uniqueness constraint on the same column.įor 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.Īt 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. ![]() 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. You might wonder if the primary key (Primary key) is not the only one that identifies a single table? What's the difference between a single constraint and a primary key? 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. Reference article: Oracle's Uniqueness Constraints (unique Constraint) usage Recommended previous post: Navicat for Oracle basic usage graphic INTRODUCTION The main text is mainly graphic, although it is a very simple basis of the article, but still hope that you have some help. This article focuses on Navicat for Oracle setting uniqueness constraints and setting a field as an ascending sequence.
0 Comments
Leave a Reply. |