What is the syntax for specifying a primary key on more than 1 column in SQLITE ? 

Sqlite primary key on multiple columns

Given the table created using: 

    CREATE TABLE tbl_Country
    (
      CountryId INT NOT NULL AUTO_INCREMENT,
      IsDeleted bit,
      PRIMARY KEY (CountryId) 
    )

How can I delete the column `IsDeleted `?

How to delete a column from a table in MySQL

I have heard the terms DDL and DML in reference to databases, but I don&#39;t understand what they are. 

What are they and how do they relate to SQL?

What are DDL and DML?

I need to add multiple columns to a table but position the columns **after** a column called `lastname`.

I have tried this:

    ALTER TABLE `users` ADD COLUMN
    (
        `count` smallint(6) NOT NULL,
        `log` varchar(12) NOT NULL,
        `status` int(10) unsigned NOT NULL
    ) 
    AFTER `lastname`;

I get this error:

&gt; You have an error in your SQL syntax; check the manual that
&gt; corresponds to your MySQL server version for the right syntax to use
&gt; near &#39;) AFTER `lastname`&#39; at line 7

&lt;br&gt;
How can I use AFTER in a query like this?

Adding multiple columns AFTER a specific column in MySQL

I would like to give a user all the permissions on a database without making it an admin.
The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same cluster so I don&#39;t want a user to be able to change production objects but it must be able to change objects on DEV.

I tried:

    grant ALL on database MY_DB to group MY_GROUP;

but it doesn&#39;t seem to give any permission.

Then I tried:

    grant all privileges on schema MY_SCHEMA to group MY_GROUP;

and it seems to give me permission to create objects but not to query\delete objects on that schema that belong to other users

I could go on by giving USAGE permission to the user on MY_SCHEMA but then it would complain about not having permissions on the table ...

So I guess my question is: is there any easy way of giving all the permissions to a user on a DB?

I&#39;m working on PostgreSQL 8.1.23.

PostgreSQL: Give all permissions to a user on a PostgreSQL database

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

I was working on my Spring boot app project and noticed that, sometimes there is a connection time out error to my Database on another server(SQL Server). 
This happens specially when I try to do some script migration with `FlyWay` but it works after several tries. 

Then I noticed that I didn&#39;t specify  `spring.jpa.hibernate.ddl-auto` in my properties file. I did some research and found that it is recommended to add
 `spring.jpa.hibernate.ddl-auto= create-drop` in development.
 And change it to: `spring.jpa.hibernate.ddl-auto= none` in production.

But I didn&#39;t actually understand how does it really work and how does hibernate generate database schema using `create-drop` or `none` value. Can you please explain technically how does it really work, and what are recommendations for using this property in development and on a production server.
Thank you

How does spring.jpa.hibernate.ddl-auto property exactly work in Spring?

I want to create a database which does not exist through JDBC. Unlike MySQL, PostgreSQL does not support `create if not exists` syntax. What is the best way to accomplish this?

The application does not know if the database exists or not. It should check and if the database exists it should be used. So it makes sense to connect to the desired database and if connection fails due to non-existence of database it should create new database (by connecting to the default `postgres` database). I checked the error code returned by Postgres but I could not find any relevant code that species the same.

Another method to achieve this would be to connect to the `postgres` database and check if the desired database exists and take action accordingly. The second one is a bit tedious to work out.

Is there any way to achieve this functionality in Postgres?

Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

I have the following table:

    CREATE TABLE child( 
      id INTEGER PRIMARY KEY, 
      parent_id INTEGER, 
      description TEXT);

How do I add a foreign key constraint on `parent_id`? Assume foreign keys are enabled.

Most examples assume you&#39;re creating the table - I&#39;d like to add the constraint to an existing one.



How do I add a foreign key to an existing SQLite table?

I&#39;m trying to programmatically add an identity column to a table Employees. Not sure what I&#39;m doing wrong with my syntax.

	ALTER TABLE Employees
	  ADD COLUMN EmployeeID int NOT NULL IDENTITY (1, 1)

	ALTER TABLE Employees ADD CONSTRAINT
		PK_Employees PRIMARY KEY CLUSTERED 
		(
		  EmployeeID
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
		ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

What am I doing wrong? I tried to export the script, but SQL Mgmt Studio does a whole Temp Table rename thing.

**UPDATE**:
I think it is choking on the first statement with &quot;Incorrect syntax near the keyword &#39;COLUMN&#39;.&quot;

Alter Table Add Column Syntax

How do I correct the error from MySQL &#39;you can only have one auto increment column&#39;.

    CREATE TABLE book (
       id INT AUTO_INCREMENT NOT NULL,
       accepted_terms BIT(1) NOT NULL,
       accepted_privacy BIT(1) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;



There can be only one auto column

I have a problem: I need to delete a column from my SQLite database. I wrote this query

    alter table table_name drop column column_name 

but it does not work. Please help me.

Delete column from SQLite table

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)

Am I correct in understanding that CREATE OR REPLACE basically means &quot;if the object exists, drop it, then create it either way?&quot;  

If so, what am I doing wrong?  This works:

    CREATE TABLE foo (id NUMBER,
    title VARCHAR2(4000) DEFAULT &#39;Default Title&#39;)

And this doesn&#39;t (ORA-00922: missing or invalid option):

    CREATE OR REPLACE TABLE foo (id NUMBER,
    title VARCHAR2(4000) DEFAULT &#39;Default Title&#39;)

Am I doing something stupid?  I don&#39;t seem to be able to find much documentation about this syntax.

How do I use CREATE OR REPLACE?

My research and experiments haven&#39;t yielded an answer yet, so I am hoping for some help.

I am modifying the install file of an application which in previous versions did not have a column which I want to add now. I do not want to add the column manually, but in the installation file and only if the new column does not already exist in the table.

The table is created as follows:

    CREATE TABLE IF NOT EXISTS `#__comm_subscribers` (
          `subscriber_id` int(11) NOT NULL auto_increment,
          `user_id` int(11) NOT NULL default &#39;0&#39;,
          `subscriber_name` varchar(64) NOT NULL default &#39;&#39;,
          `subscriber_surname` varchar(64) NOT NULL default &#39;&#39;,
          `subscriber_email` varchar(64) NOT NULL default &#39;&#39;,
          `confirmed` tinyint(1) NOT NULL default &#39;0&#39;,
          `subscribe_date` datetime NOT NULL default &#39;0000-00-00 00:00:00&#39;,
          PRIMARY KEY  (`subscriber_id`),
          UNIQUE KEY `subscriber_email` (`subscriber_email`)
        ) ENGINE=MyISAM CHARACTER SET &#39;utf8&#39; COLLATE &#39;utf8_general_ci&#39; COMMENT=&#39;Subscribers for Comm are stored here.&#39;;

If I add the following, below the create table statement, then I am not sure what happens if the column already exists (and perhaps is populated):

    ALTER TABLE `#__comm_subscribers` ADD `subscriber_surname`;
    ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default &#39;&#39;;

So, I tried the following which I found somewhere. This does not seem to work but I am not entirely sure I used it properly.

    /*delimiter &#39;//&#39;
    CREATE PROCEDURE addcol() BEGIN
    IF NOT EXISTS(
	SELECT * FROM information_schema.COLUMNS
	WHERE COLUMN_NAME=&#39;subscriber_surname&#39; AND TABLE_NAME=&#39;#__comm_subscribers&#39;
	)
	THEN
		ALTER TABLE `#__comm_subscribers`
		ADD COLUMN `subscriber_surname` varchar(64) NOT NULL default &#39;&#39;;
    END IF;
    END;
    //
    delimiter &#39;;&#39;
    CALL addcol();
    DROP PROCEDURE addcol;*/

Does anyone have a good way to do this?


add column to mysql table if it does not exist

I am working on a program that issues DDL. I would like to know whether `CREATE TABLE` and similar DDL can be rolled back in

 - Postgres 
 - MySQL 
 - SQLite 
 - et al

Describe how each database handles transactions with DDL.

Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it&#39;s a big table, INSERT or UPDATE statements could be blocked for a looooong time. Is there a way to do a &quot;hot alter&quot;, like adding a column in such a way that the table is still updatable throughout the process?

Mostly I&#39;m interested in a solution for MySQL but I&#39;d be interested in other RDBMS if MySQL can&#39;t do it.

To clarify, my purpose is simply to avoid downtime when a new feature that requires an extra table column is pushed to production. Any database schema *will* change over time, that&#39;s just a fact of life. I don&#39;t see why we should accept that these changes must inevitably result in downtime; that&#39;s just weak.

ALTER TABLE without locking the table?

How can ALTER be used to drop a column in a MySQL table if that column exists? 

I know I can use `ALTER TABLE my_table DROP COLUMN my_column`, but that will throw an error if `my_column` does not exist. Is there alternative syntax for dropping the column conditionally?

I&#39;m using MySQL version 4.0.18.


Using ALTER to drop a column if it exists in MySQL

How can I add comment to column in PostgreSQL?

    create table session_log (
                    UserId int index not null,
                    PhoneNumber int index); 

Adding comment to column when I create table in PostgreSQL?

I&#39;ve seen many times the following syntax which defines a column in a create/alter DDL statement:
 
    ALTER TABLE tbl ADD COLUMN col VARCHAR(20) NOT NULL DEFAULT &quot;MyDefault&quot;

The question is: since a default value is specified, is it necessary to also specify that the column should not accept NULLs ? In other words, doesn&#39;t DEFAULT render NOT NULL redundant ?





   

SQL Column definition : default value and not null redundant?

I have a stored function where I use temporary tables. For performance reasons, I need an index in that table. Unfortunately, I cannot use `ALTER TABLE` because this causes an implicit commit.

Therefore I&#39;m looking for the syntax to add the `INDEX` for `tempid` during creation. Can anyone be of help?

    CREATE TEMPORARY TABLE tmpLivecheck 
    (
        tmpid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
    )
    SELECT *
    FROM   tblLivecheck_copy
    WHERE  tblLivecheck_copy.devId = did;


Create a temporary table in MySQL with an index from a select

In a fairly animated discussion in my team I was made to think what most people like as primary keys. We had the following groups-

1. Int/ BigInt which autoincrement are good enough primary keys.
2. There should be at least 3 columns that make up the primary key.
3. Id, GUID and human readable row identifiers all should be treated differently.

What&#39;s the best approach for PKs? It would be awesome if you could justify your opinion. Is there a better approach that the above? 

EDIT: Anyone has a simple sample/algorithm to generate human readable identifiers for rows that scales well?


How do you like your primary keys?

I want to &quot;create or replace&quot; a trigger for a postgres table. However, there is not such sql expression.

I see that I can do a &quot;`DROP TRIGGER IF EXISTS`&quot; first (http://www.postgresql.org/docs/9.5/static/sql-droptrigger.html).

My question are:

1. Is there a recommended/better option than (`DROP` + `CREATE` trigger)
2. Is there a reason why there is not such &quot;create or replace trigger&quot; (which might imply that I should not be wanting to do it)

Note that there is a &quot;`Create or Replace Trigger`&quot; in oracle (https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm). Then, 

3. Is such command planned for Postgres at all?

Create or replace trigger postgres

I have this code:

    ALTER TABLE `settings`
    ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1

And I want to alter this table only if this column doesn&#39;t exist.

I&#39;m trying a lot of different ways, but nothing works:

    ALTER TABLE `settings`
    ADD COLUMN IF NOT EXISTS `multi_user` TINYINT(1) NOT NULL DEFAULT 1

With procedure:

    DELIMITER $$
    CREATE PROCEDURE Alter_Table()
    BEGIN
        DECLARE _count INT;
        SET _count = (  SELECT COUNT(*) 
                        FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE   TABLE_NAME = &#39;settings&#39; AND 
                                COLUMN_NAME = &#39;multi_user&#39;);
        IF _count = 0 THEN
            ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1
        END IF;
    END $$
    DELIMITER ; 

I got error in END IF, then in END and then in 1

How can I make this as simple as possible?

MySQL: ALTER TABLE if column not exists

I was trying to delete PostgreSQL user:

    DROP USER ryan;

I received this error:

&gt;     Error in query:
&gt;     ERROR: role &quot;ryan&quot; cannot be dropped because some objects depend on it
&gt;     DETAIL: privileges for database mydatabase

I looked for a solution from these threads:

 - https://stackoverflow.com/questions/3023583/postgresql-how-to-quickly-drop-a-user-with-existing-privileges
 - https://stackoverflow.com/questions/32988702/how-to-drop-user-in-postgres-if-it-has-depending-objects

Still have the same error.

This happens after I grant all permission to user &quot;ryan&quot; with:

    GRANT ALL PRIVILEGES ON DATABASE mydatabase ON SCHEMA public TO ryan;

Cannot drop PostgreSQL role. Error: `cannot be dropped because some objects depend on it`

How can I add a computed column to a table that already exists? S.O. has https://stackoverflow.com/q/5841876/456188 but no information about adding them.

How do you add a computed column to a Table?

I have &quot;read only&quot; access to a few tables in an Oracle database. I need to get schema information on some of the columns. I&#39;d like to use something analogous to MS SQL&#39;s `sp_help`. 

I see the table I&#39;m interested in listed in this query:

    SELECT * FROM ALL_TABLES

When I run this query, Oracle tells me &quot;table not found in schema&quot;, and yes the parameters are correct.

    SELECT 
    DBMS_METADATA.GET_DDL(&#39;TABLE&#39;, &#39;ITEM_COMMIT_AGG&#39;, &#39;INTAMPS&#39;) AS DDL
    FROM DUAL;

After using my Oracle universal translator 9000 I&#39;ve surmised this doesn&#39;t work because I don&#39;t have sufficient privileges. Given my constraints how can I get the datatype and data length of a column on a table I have read access to with a PL-SQL statement?

How do I get column datatype in Oracle with PL-SQL with low privileges?

I have the following table:

    CREATE TABLE child( 
      id INTEGER PRIMARY KEY, 
      parent_id INTEGER CONSTRAINT parent_id REFERENCES parent(id), 
      description TEXT);

How do I drop the constraint?

How do I DROP a constraint from a sqlite (3.6.21) table?

When I run the following in an Oracle shell it works fine

    truncate table table_name

But when I try to put it in a stored procedure

    CREATE OR REPLACE PROCEDURE test IS
    BEGIN
        truncate table table_name;
    END test;
    /

it fails with

    ERROR line 3, col 14, ending_line 3, ending_col 18, Found &#39;table&#39;, Expecting:  @   ROW  or   (   or   .   or   ;   :=

Why?

Truncating a table in a stored procedure

How to provide primary key for multiple column in a single table using PostgreSQL?   

Example:

    Create table &quot;Test&quot; 
    (
       &quot;SlNo&quot; int not null primary key,
       &quot;EmpID&quot; int not null, /* Want to become primary key */
       &quot;Empname&quot; varchar(50) null,
       &quot;EmpAddress&quot; varchar(50) null
    );

Note: I want to make `&quot;EmpID&quot;` also a primary key.

Primary key for multiple column in PostgreSQL?

In postgresql 9.4 the new JSONB was incorporated.  

On a live DB in postgresql 9.3 I have a JSON column.  

I want to migrate it to JSONB.  

Assuming I migrated the DB first to 9.4 (using pg_upgrade). What do I do next?


postgresql migrating JSON to JSONB

Can anyone tell me how I can generate the DDL for all tables, views, indexes, packages, procedures, functions, triggers, types, sequences, synonyms, grants, etc. inside an Oracle schema? Ideally, I would like to copy the rows too but that is less important.

I want to do this on a scheduled job of some kind and not manually each time, so that rules out using the wizard in SQL Developer.

Ideally, since I will be running this on several schemas that have grants and synonyms to one another, I would like to have a way to do a find/replace in the output so the schema names match whatever the names of my new schemas are going to be.

Thanks!

How to generate entire DDL of an Oracle schema (scriptable)?

With [JPA][1], DDL-generation for the attribute:

    @Column
    final String someString;

will be `someString varchar(255) null`
    
    @Column(length = 1337)
    final String someString;

will yield `someString varchar(1337) null`.

But how can I get it to produce `someString varchar(max) null`?

Is it possible using the `length`-attribute, or do I need to use the `columnDefinition`-attribute? 


  [1]: https://stackoverflow.com/q/2560125/1429387

JPA - How to set string column to varchar(max) in DDL

I keep all my functions in a text file with `&#39;CREATE OR REPLACE FUNCTION somefunction&#39;`.  
So if I add or change some function I just feed the file to psql.

Now if I add or remove parameters to an existing function, it creates an overload with the same name and to delete the original I need type in all the parameter types in the exact order which is kind of tedious.

Is there some kind of wildcard I can use to `DROP` all functions with a given name so I can just add `DROP FUNCTION` lines to the top of my file?

DROP FUNCTION without knowing the number/type of parameters?

I&#39;m looking at the MySQL docs [here][1] and trying to sort out the distinction between FOREIGN KEYs and CONSTRAINTs. I thought an FK **was** a constraint, but the docs seem to talk about them like they&#39;re separate things.

The syntax for creating an FK is (in part)...

    [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name,...)

So the &quot;CONSTRAINT&quot; clause is optional. Why would you include it or not include it? If you leave it out does MySQL create a foreign key but not a constraint? Or is it more like a &quot;CONSTRAINT&quot; is nothing more than a name for you FK, so if you don&#39;t specify it you get an anonymous FK?

Any clarification would be greatly appreciated.

Thanks,

Ethan

  [1]: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

MySQL terminology &quot;constraints&quot; vs &quot;foreign keys&quot; difference?

I&#39;m trying to write a SQL Server database update script. I want to test for the existence of a column in a table, then if it doesn&#39;t exist add the column with a default value, and finally update that column based on the current value of a different column in the same table. I want this script to be runnable multiple times, the first time updating the table and on subsequent runs the script should be ignored. My script currently looks like the following:

    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    	WHERE TABLE_NAME = &#39;PurchaseOrder&#39; AND COLUMN_NAME = &#39;IsDownloadable&#39;)
    BEGIN
    
    ALTER TABLE [dbo].[PurchaseOrder] ADD [IsDownloadable] bit NOT NULL DEFAULT 0
    
    UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref] IS NOT NULL
    
    END

SQL Server returns error &quot;Invalid column name &#39;IsDownloadable&#39;&quot;, i.e. I need to commit the DDL before I can update the column. I&#39;ve tried various permutations but I&#39;m getting nowhere fast.

All Ddl Solutions on Gang of Coders

Total of 36 Ddl Solutions