How to determine the existence of tables, columns and primary keys by Oracle

This article mainly introduces Oracle how to judge the existence of tables, columns and primary keys. It is very detailed and has certain reference value. Friends who are interested must read it!

When writing a program, the database structure often changes, so you often need to write some database scripts that need to be sent to the scene for execution. If they already exist or are repeatedly executed, some scripts will report errors, so you need to judge whether they exist. Now I will share with you some judgment methods that are often used:

one. The method of judging the existence of Oracle Table

Declare tableExistedCount number;-declare variables to store whether the table to be queried exists begin select count (1) into tableExistedCount from user_tables t where t.table_name = upper ('Test');-- query from the system table whether the table exists if tableExistedCount = 0 then-if not, create a new table execute immediate' create table Test using a quick execution statement-create a test table (ID number not null,Name = varchar2 (20) table)' End if;end

two. The method of judging the existence of columns in Oracle table

Declare columnExistedCount number;-declare variables to store the existence of begin for columns in the table to be queried-query from the system table for the existence of columns in the table select count (1) into columnExistedCount from user_tab_columns t where t.table_name = upper ('Test') and t.column_name = upper (' Age') If it does not exist, add the Age column if columnExistedCount = 0 then execute immediate 'alter table Test add age number not null'; end if;end;DECLAREnum NUMBER;BEGINSELECT COUNT (1) INTO numfrom colswhere table_name = upper (' tableName') and column_name = upper ('columnName'); IF num > 0 THENexecute immediate' alter table tableName drop column columnName';END IF;END using a quick execution statement

three. The method of judging whether there is a primary key in Oracle table

Declare primaryKeyExistedCount number;-declare variables to store the existence of begin in the columns in the table to be queried-query whether there is a primary key in the table from the system table (since a table can only have one primary key, you only need to determine the constraint type) select count (1) into primaryKeyExistedCount from user_constraints t where t.table_name = upper ('Test') and t.constraint_type =' P' -- if it does not exist, add the primary key constraint if primaryKeyExistedCount = 0 then execute immediate 'alter table Test add constraint PK_Test_ID primary key (id)'; end if;end using a quick execution statement

four. The method of judging whether there is a Foreign key in Oracle Table

Declare foreignKeyExistedCount number;-declare variables to store the existence of begin in the columns in the table to be queried-query whether there is a primary key in the table from the system table (since a table can only have one primary key, you only need to determine the constraint type) select count (1) into foreignKeyExistedCount from user_constraints t where t.table_name = upper ('Test') and t.constraint_type =' R 'and t.constraint_name =' foreign key constraint name' If it does not exist, use the quick execution statement to add the primary key constraint if foreignKeyExistedCount = 0 then execute immediate 'alter table Test add constraint foreign key constraint name foreign key references foreign key reference table (column)'. End if;end; is all the contents of the article "how to determine the existence of tables, columns and primary keys". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!