ddl - w3toppers.com

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

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis provides an overview of this issue from PostgreSQL’s perspective. Is DDL transactional according to this document? PostgreSQL – yes MySQL – no; DDL causes an implicit commit Oracle Database 11g Release 2 and above – by default, no, but an alternative called edition-based redefinition exists Older versions of Oracle – no; DDL causes an … Read more

Safely rename tables using serial primary key columns

serial is not an actual data type. The manual states: The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns The pseudo data type is resolved doing all of this: create a sequence named tablename_colname_seq create the column with type integer (or int2 / … Read more

How to convert primary key from integer to serial?

serial is a pseudo data type, not an actual data type. It’s an integer underneath with some additional DDL commands executed automatically: Create a sequence (with matching name by default). Set the column NOT NULL and the default to draw from that sequence. Make the column “own” the sequence. Details: Safely and cleanly rename tables … Read more

Can a number be used to name a MySQL table column?

From the docs: Identifiers may begin with a digit but unless quoted may not consist solely of digits. Which means you must quote it with back ticks like `25`: UPDATE table SET `25`=’100′ WHERE id=’1′

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

You can spool the schema out to a file via SQL*Plus and dbms_metadata package. Then replace the schema name with another one via sed. This works for Oracle 10 and higher. sqlplus<<EOF set long 100000 set head off set echo off set pagesize 0 set verify off set feedback off spool schema.out select dbms_metadata.get_ddl(object_type, object_name, … Read more

What are DDL and DML?

The following is adapted from here MySQL What is DDL, DML and DCL?: DDL DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database. CREATE – to create database and its objects like (table, index, views, store procedure, function and triggers). … Read more

How do you like your primary keys? [closed]

If you’re going to be doing any syncing between databases with occasionally connected apps, then you should be using GUIDs for your primary keys. It is kind of a pain for debugging, so apart from that case I tend to stick to ints that autoincrement. Autoincrement ints should be your default, and not using them … Read more

add column to mysql table if it does not exist

Here is a working solution (just tried out with MySQL 5.0 on Solaris): DELIMITER $$ DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$ CREATE PROCEDURE upgrade_database_1_0_to_2_0() BEGIN — rename a table safely IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=’my_old_table_name’) ) THEN RENAME TABLE my_old_table_name TO my_new_table_name, END IF; — add a column safely … Read more

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

For the record, the spring.jpa.hibernate.ddl-auto property is Spring Data JPA specific and is their way to specify a value that will eventually be passed to Hibernate under the property it knows, hibernate.hbm2ddl.auto. The values create, create-drop, validate, and update basically influence how the schema tool management will manipulate the database schema at startup. For example, … Read more

How to view the DDL jobs in TiDB?

You can use admin show ddl to view the current job of adding an index or the running DDL jobs. As for viewing DDL jobs, you can also use two other commands as follows: admin show ddl jobs: to view all the results in the current DDL job queue (including tasks that are running and … Read more