Using Table Just After Creating It: Object Does Not Exist

Using table just after creating it: object does not exist

Without using GO, programmatically, you would need to make 2 separate database calls.

If table exists drop table then create it, if it does not exist just create it

Just put DROP TABLE IF EXISTS `tablename`; before your CREATE TABLE statement.

That statement drops the table if it exists but will not throw an error if it does not.

table or view does not exist

In order, every object is saved with UPPER CASE name. Unless enclosed within double quotes.

Remove the double quotes to all your table names in the DDL. Also, User, State all are reserved keywords. You cannot use them as table names.

"state" --> state

OR

Refer your tables in the constraints with double quotes, same as in DDL previously..

references state(symbol) --> references "state"(symbol)

searchquery DDL ended with invalid table name because, you refer
User table, which is a datadictionary table. So, embed them in
double quotes

Example:

CREATE
  TABLE "searchquery"
  (
    query        VARCHAR2 (4000 CHAR) NOT NULL PRIMARY KEY,
    User_id NUMBER (38) NOT NULL REFERENCES "User"(anon_id),
    state_symbol VARCHAR2 (5) REFERENCES "state"(symbol),
    population_name VARCHAR2 (255) REFERENCES "population_goup"(name),
    landmark_name  VARCHAR2 (30 BYTE) REFERENCES "landmark"(name),
    event_name  VARCHAR2 (255) REFERENCES "event"(name),
    location_zip NUMBER (38) REFERENCES "landmark"(zip)
  ) ;

From DOCs.

Nonquoted identifiers are not case sensitive. Oracle interprets them
as uppercase. Quoted identifiers are case sensitive.

By enclosing names in double quotation marks, you can give the
following names to different objects in the same namespace:

employees
"employees"

"Employees"

Note that Oracle interprets the following names the same, so they
cannot be used for different objects in the same namespace:

employees
EMPLOYEES
"EMPLOYEES"

More on the Schema Object Names and Qualifiers

Check if table exists and if it doesn't exist, create it in SQL Server 2008

Something like this

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[YourTable](
    ....
    ....
    ....
) 

END

What's the difference between creating a table if it doesn't exist, and dropping the tables if they exist before creating them?

Generally when releasing db updates, it get used to compose sql scripts with statements for creating tables or other objects.

From here you may follow several strategies. Mysql seems to support also the strategy of creating one script file that you append with the updates, and so you will create the table only the first time you exec the script, all the following executions won't create the tables. Of course in this scenario you will have add the "if not exists" clause to all objects of your script file.

Instead, a more followed strategy is to create several files as many as are the db objects, one file per object. And so, if the object is a table, then first to create the table you have drop it if exists, and so explained the other statement.

Of course, when you execute this table script files you have pay close attention at the db data you want preserve from lost, and also to the order of their execution due to eventual foreign key constraints that may affects the dropping operations.

Generally this scripts are launched only in dev or test environments or when you are installing first time your db in production.

In prod env usually, as far as updating db table objects, get launched scripts that contain only alter statements.

SQL table doesn't exist

If I understand correctly, you're executing a insert query in a database dbo.RegUsers and it's giving the error Invalid object name 'dbo.RegUsers'? That simply means that table cannot find an object called "RegUsers ". There are several possible reasons for this:

The object doesn't exist, possibly because the schema and/or database don't exist
The object exists, but the database is case-sensitive and some part of the name doesn't match the name in your code

You'll need to investigate more to find out what the cause is in your case, but as a complete guess, your production server has both the RegUsers and databases?

Finally, when posting questions please always include your SQL Server version (2000/2005/2008) and edition (Express, Standard, Enterprise); they can be very important when talking about schemas and permissions, because features and behaviour can be different.

Oracle Create Table if it does not exist

Normally, it doesn't make a lot of sense to check whether a table exists or not because objects shouldn't be created at runtime and the application should know what objects were created at install time. If this is part of the installation, you should know what objects exist at any point in the process so you shouldn't need to check whether a table already exists.

If you really need to, however,

• You can attempt to create the table and catch the `ORA-00955: name is already used by an existing object" exception.
• You can query USER_TABLES (or ALL_TABLES or DBA_TABLES depending on whether you are creating objects owned by other users and your privileges in the database) to check to see whether the table already exists.
• You can try to drop the table before creating it and catch the `ORA-00942: table or view does not exist" exception if it doesn't.

---

Related Topics