I want to create a table in a SQLite database only if doesn&#39;t exist already. Is there any way to do this? I don&#39;t want to drop the table if it exists, only create it if it doesn&#39;t.

Create table in SQLite only if it doesn&#39;t exist already

Is it possible to define in which schema new tables get created by default? (Referred by &quot;unqualified table names&quot;.)

I&#39;ve seen some details about using the &quot;search path&quot; in Postgres, but I think it only works while retrieving data, not creating.

I have a bunch of SQL scripts, which create many tables. Instead of modifying the scripts, I want to set the database create tables in a specific schema by default - when they have unqualified names.

Is this possible?

How does the search_path influence identifier resolution and the &quot;current schema&quot;

I have a database with a table Customers that have some data&lt;br&gt;&lt;br&gt;
I have another database in the office that everything is the same, but my table Customers is empty&lt;br&gt;&lt;br&gt;
How can I create a sql file in SQL Server 2005 (T-SQL) that takes everything on the table Customers from the first database, creates a, let&#39;s say, buildcustomers.sql, I zip that file, copy it across the network, execute it in my SQL Server and voila! my table Customers is full&lt;br&gt;
&lt;br&gt;
How can I do the same for a whole database?

SQL Server 2005 - Export table programmatically (run a .sql file to rebuild it)

I know the statement:

    create table xyz_new as select * from xyz;

Which copies the structure and the data, but what if I just want the structure?


How can I create a copy of an Oracle table without copying the data?

If I want to save any changes in a table, previously saved in SQL Server Management Studio (no data in table present) I get an error message:

&gt; Saving changes is not permitted. The changes you have made require the
&gt; following tables to be dropped and re-created. You have either made
&gt; changes to a table that can&#39;t be re-created or enabled the option
&gt; Prevent saving changes that require the table to be re-created.

What can prevent the table to be easily edited? Or, is it the usual way for SQL Server Management Studio to require re-creating table for editing? What is it - this **&quot;option Prevent saving changes&quot;**?


Saving changes after table edit in SQL Server Management Studio

I changed the datadir of a MySQL installation and all the bases moved correctly except for one. 
I can connect and `USE` the database. `SHOW TABLES` also returns me all the tables correctly, and the files of each table exists on the MySQL data directory.

However, when I try to `SELECT` something from the table, I get an error message that the table does not exist. Yet, this does not make sense since I was able to show the same table through `SHOW TABLES` statement. 

My guess is that `SHOW TABLES` lists file existence but does not check whether a file is corrupted or not. Consequently, I can list those files but not access them.

Nevertheless, it is merely a guess. I have never seen this before. Now, I cannot restart the database for testing, but every other application that uses it is running fine.
But that&#39;s just a guess, I&#39;ve never seen this before. 

Does anyone know why this is happening?

Example:

    mysql&gt; SHOW TABLES;
    +-----------------------+
    | Tables_in_database    |
    +-----------------------+
    | TABLE_ONE             |
    | TABLE_TWO             |
    | TABLE_THREE           |
    +-----------------------+
    mysql&gt; SELECT * FROM TABLE_ONE;
    ERROR 1146 (42S02): Table &#39;database.TABLE_ONE&#39; doesn&#39;t exist



MySQL &gt; Table doesn&#39;t exist. But it does (or it should)

In a MySQL script you can write:

    CREATE TABLE IF NOT EXISTS foo ...;

... other stuff ...

and then you can run the script many times without re-creating the table.

How do you do this in PostgreSQL?

PostgreSQL create table if not exists

What is the best way to get the names of all of the tables in a specific database on SQL Server?

How do I get list of all tables in a database using TSQL?

I have a table:

    table votes (
        id,
    	user,
    	email,
    	address,
    	primary key(id),
    );

Now I want to make the columns **user, email, address** unique (together).

How do I do this in MySql?

Of course the example is just... an example. So please don&#39;t worry about the semantics.

How do I specify unique constraint for multiple columns in MySQL?

This is probably a n00blike (or worse) question. But I&#39;ve always viewed a schema as a table definition in a database. This is wrong or not entirely correct. I don&#39;t remember much from my database courses. 

What is the difference between a schema and a table and a database?

What is the upper limit of records for MySQL database table. I&#39;m wondering about autoincrement field. What would happen if I add milions of records? How to handle this kind of situations?
Thx!

Maximum number of records in a MySQL database table

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL?

At the moment I&#39;ve managed to come up with a SQL statement that returns all the commands I need to execute: 

    SELECT &#39;TRUNCATE TABLE &#39; ||  tablename || &#39;;&#39; FROM pg_tables WHERE tableowner=&#39;MYUSER&#39;;

But I can&#39;t see a way to execute them programmatically once I have them.

Truncating all tables in a Postgres database

If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?

    GO
    IF OBJECT_ID(&#39;dbo.[Course]&#39;,&#39;U&#39;) IS NOT NULL
        DROP TABLE dbo.[Course]
    GO
    IF OBJECT_ID(&#39;dbo.[Student]&#39;,&#39;U&#39;) IS NOT NULL
        DROP TABLE dbo.[Student]

SQL DROP TABLE foreign key constraint

Mysql: Select rows from a table that are not in another

I have always wondered how Facebook designed the friend &lt;-&gt; user relation.

I figure the user table is something like this:

    user_email PK
    user_id PK
    password 

I figure the table with user&#39;s data (sex, age etc connected via user email I would assume). 

How does it connect all the friends to this user?

Something like this?
 
    user_id
    friend_id_1
    friend_id_2
    friend_id_3
    friend_id_N 

Probably not. Because the number of users is unknown and will expand.

 

Facebook database design?

Is it possible to search every field of every table for a particular value in Oracle?

There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is `1/22/2008P09RR8`.
&lt;

I&#39;ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.

    SELECT * from dba_objects 
    WHERE object_name like &#39;%DTN%&#39;

There is absolutely no documentation on this database and I have no idea where this field is being pulled from.

Any thoughts?

Search All Fields In All Tables For A Specific Value (Oracle)

This example is taken [from w3schools](http://www.w3schools.com/sql/sql_primarykey.asp). 

    CREATE TABLE Persons
    (
        P_Id int NOT NULL,
        LastName varchar(255) NOT NULL,
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255),
        CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
    )

My understanding is that both columns together (`P_Id` and `LastName`) represent a primary key for the table `Persons`. Is this correct?

 - Why would someone want to use multiple columns as primary keys instead of a single column? 
 - How many columns can be used together as a primary key in a given table?


Why use multiple columns as primary keys (composite primary key)

I store various user details in my MySQL database. Originally it was set up in various tables meaning data is linked with UserIds and outputting via sometimes complicated calls to display and manipulate the data as required. Setting up a new system, it almost makes sense to combine all of these tables into one big table of related content.

- Is this going to be a help or hindrance?
- Speed considerations in calling, updating or searching/manipulating?

Here&#39;s an example of some of my table structure(s):

- users - UserId, username, email, encrypted password, registration date, ip
- user_details - cookie data, name, address, contact details, affiliation, demographic data
- user_activity - contributions, last online, last viewing
- user_settings - profile display settings
- user_interests - advertising targetable variables
- user_levels - access rights
- user_stats - hits, tallies

---
**Edit:** I&#39;ve upvoted all answers so far, they all have elements that essentially answer my question.

Most of the tables have a 1:1 relationship which was the main reason for denormalising them.

Are there going to be issues if the table spans across 100+ columns when a large portion of these cells are likely to remain empty?

Which is more efficient: Multiple MySQL tables or one large table?

I&#39;m looking for a way to find the row count for all my tables in Postgres.  I know I can do this one table at a time with:

    SELECT count(*) FROM table_name;

but I&#39;d like to see the row count for all the tables and then order by that to get an idea of how big all my tables are.



How do you find the row count for all your tables in Postgres

How can I import a CSV file into a MySQL table?  I would like for the first row of data be used as the column names.

I read https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table, but the only answer was to use a GUI and not a shell? 

How to import a CSV file into a MySQL table

How to take table-level backup (dump) in MS SQL Server 2005/2008?

Table-level backup

I know you can ALTER the column order in MySQL with FIRST and AFTER, but why would you want to bother? Since good queries explicitly name columns when inserting data, is there really any reason to care what order your columns are in in the table? 

Is there any reason to worry about the column order in a table?

I need to send a SQL query to a database that tells me how many rows there are in a table. I could get all the rows in the table with a SELECT and then count them, but I don&#39;t like to do it this way. Is there some other way to ask the number of the rows in a table to the SQL server?

SQL count rows in a table

Is it possible to add a column to a table at a specific ordinal position in Microsoft SQL Server?

For instance, our tables always have CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy columns at the &quot;end&quot; of each table definition? I&#39;d like the new column to show up in SSMS above these columns. 

If I am scripting all my database changes, is there a way to preserve this order at the end of the table? 


FYI, I&#39;m not trying to institute a flame war on if this should even be done. If you want to read about a thread that degenerates quickly into that, here&#39;s a good one:

[http://www.developersdex.com/sql/message.asp?p=581&amp;r=5014513][1]


  [1]: http://www.developersdex.com/sql/message.asp?p=581&amp;r=5014513

Add a new table column to specific ordinal position in Microsoft SQL Server

I have a SQL Server 2012 database called `MyDatabase`. How can I find how many tables are in the database?

I&#39;m assuming the format of the query would be something like the following, but I don&#39;t know what to replace `database_tables` with:

    USE MyDatabase
    SELECT COUNT(*)
    FROM [database_tables]

Count the Number of Tables in a SQL Server Database

I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do this?

Copy tables from one database to another in SQL Server

I need a table variable to store the particular rows from the table within the [MySQL][1] procedure.
E.g. declare @tb table (id int,name varchar(200))

Is this possible? If yes how?

  [1]: http://en.wikipedia.org/wiki/MySQL


All Database Table Solutions on Gang of Coders

Total of 27 Database Table Solutions

Create table in SQLite only if it doesn't exist already

How does the search_path influence identifier resolution and the "current schema"