Shulou(Shulou.com)06/01 Report--

Preface

Recently, a careful colleague at work found that there are some duplicate check constraint checks in the full sql script of the product, like the following figure

Repeat the script

The weird thing is that each time the script is executed and then the script is exported, the number of repeats in the exported script is increased. Through navicat, finally confirm that each import adds a duplicate check constraint, as shown in the following figure

Navicat

This full script is exported directly from the database. In order to facilitate the import of other Oracle databases, the service name and double quotation marks are manually removed when exporting from the product shipping library.

The problem can be reproduced by following these steps:

1. Create a tabl

CREATE TABLE PD_WEB_FILEUPLOAD_CHUNK (ID VARCHAR2 (32 BYTE) NOT NULL, MD5 VARCHAR2 (32 BYTE) DEFAULT NULL NULL, CHUNK NUMBER DEFAULT NULL NULL, FILE_DIR VARCHAR2 (200 BYTE) NOT NULL)

You can see the NOT NULL logo in the above script. After execution, you can see the result in navicat as follows.

Navicat

Notice that the check constraint here is in double quotes.

two。 Execute scripts that add check constraints

ALTER TABLE PD_WEB_FILEUPLOAD_CHUNK ADD CHECK (ID IS NOT NULL); ALTER TABLE PD_WEB_FILEUPLOAD_CHUNK ADD CHECK (FILE_DIR IS NOT NULL)

After executing it twice, the result is as shown in the figure

Navicat

At this point in the test, I thought I finally found the cause and confirmed it as the problem of double quotes. With a rigorous attitude, I reconfirmed it.

3. A script that executes check constraints in double quotes

ALTER TABLE PD_WEB_FILEUPLOAD_CHUNK ADD CHECK ("ID" IS NOT NULL); ALTER TABLE PD_WEB_FILEUPLOAD_CHUNK ADD CHECK ("FILE_DIR" IS NOT NULL)

The result is shown in the figure:

Navicat

Oh, shit! Unexpectedly, it will still be generated repeatedly!

Solution method

Verification here, it is considered to find out the reason.

In the fully exported script, the check constraint is already implied in the script that creates the table, and if the check constraint is added again, it will be generated repeatedly. Therefore, the solution is to manually delete all displayed check constraints.

Summary

The above is the whole content of this article, I hope that the content of this article can bring some help to your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.