Questions tagged [ddl]

Data Definition Language; defining database structures but not their contents.

Filter by
Sorted by
Tagged with
2 votes
2 answers
3k views

How do I get the DDL used to create function based index?

I am reverse-engineering the schema of an Oracle database. I have a table and I created a function-based index on it. DROP TABLE Fbi_tab; CREATE TABLE Fbi_tab ( a INTEGER, b INTEGER, c ...
user674669's user avatar
1 vote
3 answers
5k views

How to determine if a column already exists in a MySQL table before altering the table to add it?

I am trying to determine if a column already exists in a MySQL table before altering the table to add it. I need an all in one query I can run since I am executing this through an old PHP mysql ...
lolcode's user avatar
  • 113
3 votes
2 answers
8k views

Adding a new primary key ID column to an existing table

I want to add a new column to a table. The table is already populated with data. I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it. I ...
Adrian Hodgson's user avatar
16 votes
1 answer
18k views

Why is adding a NOT NULL column with a default constraint instantaneous?

CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100)) INSERT INTO TestTab (st) values ('a') INSERT INTO TestTab (st) values ('b') INSERT INTO TestTab (st) values ('c') INSERT INTO TestTab (st)...
Akash's user avatar
  • 1,012
8 votes
1 answer
581 views

Does SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime?

Putting together a quickie proc to help with debugging, I ran into a what seems to be a error in the compiler. create proc spFoo @param bit as begin if @param = 0 begin select * ...
Peter Vandivier's user avatar
5 votes
1 answer
4k views

"TRUNCATE doesn't run ON DELETE trigger" true or false [closed]

I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here Why is truncate DDL? I this answer I found a sentence as given below "The fact ...
geeksal's user avatar
  • 151
5 votes
3 answers
4k views

Recreate all foreign keys in all tables as deferrable (batch)

I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?
Vlad's user avatar
  • 445
1 vote
0 answers
132 views

MySQL Cluster "ALTER ONLINE TABLE ... STORAGE MEMORY" doubt

I have a MySQL Cluster database where the data is both in-memory and written to disk. I'm trying to understand the ALTER ONLINE TABLE ... STORAGE MEMORY statement, so that I can use this technique to ...
nulll's user avatar
  • 151
6 votes
2 answers
10k views

Basic transactional DDL script in PostgreSQL

PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
user574835783's user avatar
4 votes
1 answer
908 views

What's the difference between data definition language and data description language?

Are they the same thing? Which one does DDL stand for? Here's what my understanding is, but could potentially be incorrect: Data definition language: Contains names and types and field length etc. of ...
tsp216's user avatar
  • 165
2 votes
1 answer
251 views

help in correcting the syntax error of the below create table with as with multiple select [closed]

Please help to correct the syntax of the below. I am from a math background trying to use Oracle. The command helps me permute the columns and put them in one table. create table newsample AS ( ...
sriram's user avatar
  • 29
4 votes
1 answer
7k views

Create a function from another function

Is it possible to create a new function from another plpgsql function? Something like this: CREATE OR REPLACE FUNCTION func_test() RETURNS VOID AS $BODY$ BEGIN CREATE OR REPLACE FUNCTION func_test2(...
Skeeve's user avatar
  • 143
1 vote
1 answer
632 views

Trigger to drop table only if there are no records

How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?
F_Face's user avatar
  • 21
1 vote
1 answer
28 views

No priviligies to create table in custom database

I'm using locally installed express edition of SQL server. CURRENT_USER = dbo, and I used win authentication. The database was successfully created this way: CREATE DATABASE [online_banking] ON ...
Rocketq's user avatar
  • 113
5 votes
1 answer
10k views

"ERROR: there is no parameter $1" in "EXECUTE .. USING ..;" statement in plpgsql

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this: CREATE TABLE parent_table ( value integer, end_time timestamp without time zone ); CREATE OR ...
donatello's user avatar
  • 275
5 votes
1 answer
5k views

Trigger actions on database creation or attach

On our dev environment we need to run a bunch of actions when some databases are created or attached. Actions like cleaning up some tables, reseeding some others, changing emails, etc. I was trying to ...
Yaroslav's user avatar
  • 2,826
8 votes
4 answers
10k views

Copy indexes from one table to another

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
Kyle Decot's user avatar
3 votes
2 answers
765 views

How to capture sp_refreshsqlmodule event

My issue: executing sp_refreshsqlmodule will update the sys.objects.modify_date value, but does not fire a DDL event that can be used by a DDL trigger. So if using a DDL trigger to show object change ...
BateTech's user avatar
  • 746
6 votes
1 answer
792 views

PostgreSQL: Add or drop column under load give wrong number of columns errors

I have a heavily used PostgreSQL database. Sometimes I need to add/remove columns, preferably without any service interruptions. I follow the safe operations list from https://www.braintreepayments....
viblo's user avatar
  • 479
10 votes
1 answer
5k views

Reclaim disk space from dropped column without downtime

I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used. The docs suggest doing a table rewriting ...
viblo's user avatar
  • 479
5 votes
1 answer
28k views

Get the DDL for all indexes - Oracle

I have about 5 schemas in my oracle 12c database for which I need the DDL for all the indexes for each of the schemas. DDL I have checked it on how to get the code from it, but for all the indexes and ...
tesla747's user avatar
  • 1,876
9 votes
2 answers
7k views

Use composite type to create new table

In ORDBMS databases I have seen that complex types can be used: create type name as( ...) And I can references those types when creating a new table, like: create table example (row_name ref(name)) ...
lcjury's user avatar
  • 247
15 votes
2 answers
2k views

Dropping a table with a non-printable character in its name [duplicate]

I have a legacy table with a name containing non-printable characters (CHAR(31), to be specific). The non-printable character is beside a underscore, and I've discovered that the shortcut CTRL+SHIFT+...
Thiago Anitelle's user avatar
7 votes
1 answer
22k views

The ALTER TABLE statement conflicted with the CHECK constraint

I need to check datecom is less than datelivr create table Commande ( Numcom int identity primary key, Datecom date, Datelivr date, Codecli int foreign key references clients (Codecli) ) alter ...
Khalil B's user avatar
-4 votes
1 answer
1k views

how to create a synonym in Oracle? [closed]

some one tell me the basic syntax for creating synonym for the table employees create synonym for emp for app.employees while creating it shows error ORA-00995: missing or invalid synonym ...
S.A.K's user avatar
  • 3
2 votes
1 answer
1k views

Determine the command type in a DDL trigger / get names and datatypes of new columns

Given strings corresponding to DDL commands, how can I determine which DDL command (column addition, constraint addition etc.) it precisely is? I have a DDL trigger on a database that will be fired ...
Ritesh Bhakre's user avatar
31 votes
3 answers
14k views

Constraint to enforce "at least one" or "exactly one" in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address | ...
Kevin Burke's user avatar
1 vote
1 answer
189 views

PgTap, unit test a foreign key is deferrable

I am using PgTap to unit test a Postgres (v9.4) database structure in a continuous migration process. It works great. Here is my question: « Is there a way to control that a foreign key constraint is ...
greg's user avatar
  • 255
5 votes
2 answers
7k views

Does PostgreSQL support online schema modification (DDL)?

Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table. Does PostgreSQL support this to any level? That is, can I add/drop columns from a table ...
dukebody's user avatar
  • 175
1 vote
2 answers
775 views

Default constraint issue

I have a table TempTable with one column having a default constraint ALTER TABLE [dbo].[TempTable] ADD CONSTRAINT [DF_TempTable_Version] DEFAULT ((1)) FOR [Version] Version is defined as decimal ...
Jeena's user avatar
  • 137
1 vote
1 answer
1k views

Is there a way to add a column to a table without affecting performance?

How do you add a column to a table in production without affecting its performance? Or is this impossible? I'm asking about Postgres.
Propulsion's user avatar
10 votes
3 answers
12k views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is not ...
Egidi's user avatar
  • 373
3 votes
4 answers
8k views

Find constraint disabled in SQL Server

I'm doing an audit of constraints in SQL Server. How to find all constraints that are not enabled? This, after using the next declaration: alter table mytable nocheck constraint all
Diego Flores's user avatar
6 votes
1 answer
7k views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
xchg's user avatar
  • 105
0 votes
1 answer
142 views

How to store a version sequence per product

I need to create a table like the following one: +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 |...
Jyrkka's user avatar
  • 137
8 votes
3 answers
6k views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
burnsy's user avatar
  • 275
4 votes
1 answer
688 views

Circular dependency in schema or boolean with CHECK?

Let's say I have the following tables: create table repositories ( id serial primary key, name varchar(255) not null ); create table remotes ( id serial primary key, url varchar(255) ...
Florian Margaine's user avatar
3 votes
2 answers
1k views

I really can't get rid of these conditional statements in mysqldump?

My mysqldump DDL file is filled with tons of this: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`... I've searched to no avail for an option to silence this. ...
Ifedi Okonkwo's user avatar
1 vote
1 answer
800 views

Generate create table DDL from any arbitrary select query? [closed]

Using allroundautomations PL/SQL Developer, is there a way, given any arbitrary select query like this: select myfavoritenum, myfavoritetxt from tablewithmanycolumns to magically receive a create ...
amonroejj's user avatar
  • 123
0 votes
1 answer
51 views

IS-A relation when defining tables

I already asked this question on SO, prior to realizing that there may have been a more suitable place. I am trying to implement an IS-A relationship between tables. Say I have a singer and a band ...
Airwavezx's user avatar
  • 101
17 votes
2 answers
62k views

How can I map a login to a database using T-SQL (not SSMS)

I'm writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part: I just want to do the equivalent of clicking the little box under "Map" for ...
eddie_cat's user avatar
  • 559
0 votes
1 answer
187 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
boatcoder's user avatar
  • 335
0 votes
2 answers
2k views

Transaction has set autocommit = 0, but still the update happens before I commit

I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of ...
user60611's user avatar
1 vote
1 answer
15k views

All users are having NO Privileges in phpMyAdmin

By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one ...
a989898's user avatar
  • 11
1 vote
2 answers
2k views

Oracle SQL Creating Copy of Table Using DBMS_metadata

so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this ...
Blued00d's user avatar
1 vote
1 answer
7k views

How to track only DDL statements executed in MySQL

Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements. I had gone through the ...
Phanindra's user avatar
  • 1,007
27 votes
2 answers
4k views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
sennett's user avatar
  • 370
37 votes
2 answers
79k views

How do I drop all constraints from all tables?

I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from ...
Aaron Bertrand's user avatar
1 vote
2 answers
164 views

Best approach to maintain a single set of DDLs and DMLs for multiple database type

In our application, we support multiple types of databases namely Oracle, IBM DB2 (both UDB and ZOS), postgresql and So on. Currently we are maintaining separate DDLs and DMLs for each of these ...
musibs's user avatar
  • 123
0 votes
1 answer
817 views

Alter trigger Oracle

Does alter trigger hang if a table is locked by some DML operation ? What kind of exception does it throw? Resource busy 0054 or some other exception...? I have tried to reproduce this scenario but ...
bluefish_stkx's user avatar