Questions tagged [ddl]
Data Definition Language; defining database structures but not their contents.
52
questions
52
votes
2
answers
59k
views
Best way to populate a new column in a large table?
We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
- 745
57
votes
2
answers
55k
views
MATCH FULL vs MATCH SIMPLE in foreign key constraints
I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs.
The default is MATCH SIMPLE. How do they function?
user32234
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 ...
- 370
18
votes
3
answers
86k
views
Reset IDENTITY value
I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
- 333
24
votes
1
answer
17k
views
How to use transactions with SQL Server DDL?
I have a login table into which all inserts are done by a single stored procedure.
CREATE TABLE dbo.LogTable(
LogRefnr int IDENTITY(1, 1) NOT NULL,
LogQuery varchar(255) NOT NULL,
LogTime ...
- 12.2k
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, ...
- 275
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 | ...
- 805
4
votes
1
answer
3k
views
CREATE SEQUENCE using expressions with psql variables for parameters
I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression.
The specific goal is to automate create-scripts for ...
- 43
16
votes
5
answers
55k
views
MySQL - fastest way to ALTER TABLE for InnoDB
I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices.
I want to change the name of one of the columns and add a few more indices.
What is the fastest way to ...
- 1,513
14
votes
2
answers
13k
views
Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?
In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
- 545
29
votes
8
answers
70k
views
Query to compare the structure of two tables in MySQL
To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version).
Can you think of a query that can compare two tables?...
- 747
16
votes
3
answers
32k
views
Why is truncate DDL?
I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ?
Q....
- 1,545
15
votes
5
answers
23k
views
Is there a means to set the owner of all objects in a PostgreSQL database at the same time?
The Stack Overflow Q & A Modify OWNER on all tables simultaneously in PostgreSQL describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however ...
- 1,221
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))
...
- 247
5
votes
1
answer
15k
views
Check constraint references another column in same table
I have this table but check constraint is throwing me an error:
Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'.
I've seen this as allowed ...
user37362
3
votes
1
answer
11k
views
How to generate create table script without primary and foreign key in Oracle
I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.
SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','...
- 433
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 |...
- 137
18
votes
2
answers
39k
views
Disabling Checking of Schema On Function/Stored Procedure Creation
I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts ...
- 587
15
votes
3
answers
34k
views
CREATE TABLE LIKE another table but with additional columns
Is there a way to create a table that has the same structure as another table, but with additional columns?
From this question, I can copy a table's structure with:
CREATE TABLE new_table_name ( like ...
- 255
14
votes
4
answers
29k
views
Script out Oracle DDL in an automated fashion
Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention.
I know of DBMS_METADATA.get_ddl(), but have found that the ...
- 315
13
votes
2
answers
9k
views
How to check if the SYSTEM_VERSIONING for a table is ON?
I know that SQL Server 2016 let us to use the SYSTEM_VERSIONING like :
CREATE TABLE EmpSalary
(
EmpID int NOT NULL PRIMARY KEY CLUSTERED
, SalaryAmt decimal (10,2) NULL
, ...
- 131
11
votes
3
answers
37k
views
Why can't we write ddl statement directly into the PL/SQL block
Why can't we write ddl statements directly in PL/SQL block, for example when i write
CREATE OR REPLACE PROCEDURE test IS
BEGIN
truncate table table_name; // error
END test;
/
But,
CREATE OR ...
- 1,545
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 ...
- 373
9
votes
3
answers
2k
views
Trigger to change Database collation on creation
I'm trying to create a Trigger, to alter collation of a database on its creation, but how can I catch the database name to use inside the trigger?
USE master
GO
CREATE TRIGGER ...
- 7,374
9
votes
3
answers
1k
views
Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?
In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT,
CREATE TABLE foo ( a int );...
- 62.1k
9
votes
1
answer
29k
views
How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?
I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro.
I have so far looked at DBMS_METADATA.GET_DDL commands to generate DDL. I know that I can import an Access DB into ...
kg.
8
votes
4
answers
12k
views
Is there a way in PostgreSQL to set an default owner for all future create table statements in a session
We have a program that transfers cobol data (index files) to a database.
Until now we transferred the data to an oracle database.
In the beginning of the program I logged in with user sys and ...
- 185
8
votes
2
answers
11k
views
What is the best way to create Primary Key on a large table in Postgresql 9.5?
I have to create a primary key on a large table (~100Million records) in Postgtres database. What is the best and fastest way to create pkey? This column is a sequence column and i don't want to lock ...
- 81
8
votes
3
answers
2k
views
Create function in central database or repeat in each database?
One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
- 377
7
votes
1
answer
15k
views
How to create a partitioned table based on date?
I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an ...
- 5,333
6
votes
2
answers
13k
views
SQL Server Concurrent Inserts and Deletes
From: SQL Server 2014 Concurrent input issue
Follow up question:
How do we Delete and Reinsert rows in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc? Do we ...
user129291
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 ...
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 ...
- 105
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 ...
- 151
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 ...
- 2,826
5
votes
2
answers
2k
views
SQL Server (2012) non-blocking DDL?
I'm not very experienced with SQL Server, so maybe I'm missing something
My situation is as follows:
Session 1 runs a CREATE TABLE (or other CREATE statements) with autocommit off and the CREATE is ...
user1822
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 ...
- 275
4
votes
1
answer
14k
views
PostgeSQL: create role/user if not exists?
Is there some way to create a user/role/group in PostgreSQL via SQL only if it does not exist yet? The only solution I have found so far is creating a custom stored procedure, since that supports ...
- 154
3
votes
3
answers
5k
views
if block fails to create temp table in a procedure
I'm attempting to do this in a procedure:
DECLARE @a bit = 1;
BEGIN
SELECT * INTO #aTemp FROM OPENROWSET( ... );
IF @a = 0
BEGIN
SELECT ... INTO #bTemp FROM #aTemp;
END
...
- 1,194
3
votes
3
answers
24k
views
exporting DDL of all views Oracle
How can I go about exporting DDL of all my schema views in single .sql file ?
I've read there is a function DBMS_METADATA.GET_DDL But I can't figure out how to use it for my case.
I know how to ...
- 629
3
votes
1
answer
1k
views
Postgres varchar(100)[] -> text[] column conversion
I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in ...
- 283
3
votes
1
answer
23k
views
Is there a way to truncate table that has foreign a key?
Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?
- 557
2
votes
0
answers
1k
views
Cannot drop a Postgres table
I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop.
I need to drop a few tables in my Postgres DB. I tried the ...
- 21
2
votes
1
answer
174
views
Change column without interrupting service
We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
- 21
1
vote
1
answer
3k
views
Add a column with Not Null [duplicate]
I have a existing table in which i am adding a column as NgoId whose datatype is int. I want to add as NOT NULL to the column .
When I'm adding this I am getting the following error:
Unable to ...
- 203
1
vote
1
answer
1k
views
Upgrade production database during software development lifecycle
Background
Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be ...
- 833
1
vote
3
answers
8k
views
SQL Permission to Truncate Table
How Do I give permission for user to Truncate all tables in database?
The minimum permission required is 'ALTER on table_name' per articles below. With this, how do I give permission to allow user to ...
user129291
0
votes
1
answer
1k
views
SELECT INTO with regexp_replace() doesn't write changes into newly generated table
I have been using this query in Postgres 9.1 in order to remove '' from existing data.
Below is a sample of the data before running regexp_replace():
6''
6''
6''
20''
12''
18''
20''
8''
10''
''
''
...
- 31
0
votes
1
answer
41
views
How to rapidly compare definitions of many tables of two PostgreSQL databases?
I have two PostgreSQL databases, the one for producing, and the other for testing.
Both of them have a lot of tables with same table names.
Now I need to upgrade the producing system to the new ...
- 305
0
votes
1
answer
159
views
Mysql : what are the steps for creating a database schema for a big project with many tables?
I am creating a project with lots of tables like one for taxes, one for employees, customers etc. So how should I go about it?
For eg: building the ER model first or something like that
- 93