Questions tagged [ddl]

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

Filter by
Sorted by
Tagged with
2
votes
1answer
72 views

Disabling triggers makes DELETE fast, but the table has no triggers?

I am deleting 130k out of 750k rows from a PostgreSQL table. The first time, it took 8 hours to complete the delete query. Second time, I added an index to the table, and rebuilt that index. Now it ...
1
vote
1answer
50 views

ERROR 1180 (HY000): Got error 5 during COMMIT

We're using Galera cluster and I'm running below statement to make a new table: create table tab2 select distinct id , report from my_table ; It runs for sometime and after that it fails/cancel ...
2
votes
2answers
47 views

Convert postgres varchar[] to text[] without downtime

As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes. Is there a way to manage this conversion without the ...
3
votes
1answer
33 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 ...
1
vote
1answer
24 views

Can I make postgres' pg_dump condense the alter table statements in the create table section?

If I use pg_dump to get the schema from my database, I get separate sections for things like keys, constraints etc., like this: CREATE TABLE public.account ( id uuid NOT NULL, db_version ...
0
votes
1answer
38 views

Online schema changes for cloudsql DB

I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime. What I have tried so far: I tried with pt-online-schema-...
1
vote
1answer
21 views

Retrieve Names of Check Constraints Specific to a Column

PostgreSQL 12 When I rename a column in a table, I would like to dynamically rename the matching check constraints as well. I have a hypothetical table: CREATE TABLE hypothetical_table ( id SERIAL ...
0
votes
2answers
31 views

Add a column with name as a result from a SELECT

I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table. ...
0
votes
2answers
67 views

Can't rely on INFORMATION_SCHEMA.view_definition?

I've built application that compares objects in the databases and generates scripts to replicate objects in the different DB. What I've noticed while I was using this to retrieve view code select * ...
0
votes
0answers
35 views

Oracle database, convert XMLTYPE table from basicfile lob to binary xml

Trying to import an Oracle EBS database (currently in a 19c staging database) into 19c Autonomous Data Warehouse, which won't accept XMLTYPE tables with "STORE AS BASICFILE LOB", but will ...
1
vote
1answer
161 views

Does InnoDB DROP INDEX release disk space without OPTIMIZE TABLE?

We are using MySQL 5.6 InnoDB storage engine and innodb_file_per_table is ON. We have some huge tables (> 100GB) with hundreds of millions of rows. For some huge tables, index space accounts for ...
-3
votes
1answer
1k views

What is “ENGINE=InnoDB DEFAULT CHARSET=utf8mb4” for? [closed]

I'm exploring Sakila database. After CREATE TABLE, I saw this syntax. Could any one help to let me know what is this and what it is for? how and when to use it? (I didn't see this in my SQL course) ...
0
votes
1answer
37 views

rename a column without disrupting users

You can rename a column using the following statement: ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; However, this brings the old column out of commission immediately. Any use ...
0
votes
2answers
392 views

get DDL from Mongo database

I haven't done much with MongoDB. But in trying to understand the data that we do have in MongoDB, it makes sense to start with looking at the DDL in the database. What is the simplest way to pull the ...
0
votes
1answer
29 views

is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra

Is there a DDL generator out there where you can start with CSV/TSV (or maybe even JSON) to create DDL for Cassandra?
2
votes
1answer
45 views

Implications of removing unnecessary column from primary key

I have a database in which a number of tables are defined with multiple UUID columns forming the Primary Key when they should instead be defined as Foreign Keys, e.g. CREATE TABLE [dbo].[SomeTable]( ...
0
votes
0answers
27 views

Oracle 12cR2 - ORA-00905 When Creating View

I want to create a view as below: CREATE OR REPLACE VIEW "USERNAME"."VIEWNAME" ("columnName", "columnName", "columnName", ...) AS SELECT DISTINCT ...
0
votes
1answer
59 views

Oracle 12cR2 - How To Create DDL Script Of A View With All Dependencies and Permissions

I have a view in my production database and I want to create same view in my test database. I tried this commmand: ‌‌select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual; It ...
1
vote
1answer
17 views

Create A Pivot With No SUM() in MySQL

I've seen a lot of examples using MySQL to create a PIVOT but they all use SUM() - using this DDL how can I create a PIVOT in MySQL Create Table `data` ( callDate text ,callerName text ,...
-1
votes
3answers
24 views

Set The Order Query Results Display

I need to display my emailType in DESC order. Now it is a varchar field so I need it to be reverse alphabet order Z - A. When I run my query it is displayed in ASC order, and since I am using a ...
0
votes
1answer
53 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
4
votes
1answer
369 views

How to safely replace the old table with its cleaned copy?

I have created a table_cleaned like table and inserted unique values from table into table_cleaned. Now I want to replace table with table_cleaned with DROP TABLE table; ALTER TABLE table_cleaned ...
0
votes
1answer
23 views

How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?

I created a table in HeidiSQL: CREATE TABLE `publish_dt` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `TS` DATETIME NULL DEFAULT NULL, `Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci'...
1
vote
1answer
98 views

How to get SHOW CREATE TABLE without table borders made of dashes?

When I use SHOW CREATE TABLE in my MariaDb command line client I get this output for my table: +---------+----------------------------------------------------------------------------------------------...
0
votes
1answer
36 views

How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?

We are dealing with very large tables in Sql Azure. Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends. Metadata operations are quick ...
1
vote
1answer
208 views

lock-ddl-per-table option not working as expected in Xtrabackup

I've recently been getting the following errors on some of my Percona XtraBackup backups: InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have ...
1
vote
0answers
87 views

Is it possible to alter normal column to virtual column in Oracle?

ALTER TABLE TAB_NAME ADD TEST_COL VARCHAR2(255); ALTER TABLE TAB_NAME MODIFY TEST_COL GENERATED ALWAYS as ('some_exp') VIRTUAL; Second script gives an error: Error report - ORA-54026: Real column ...
1
vote
1answer
313 views

Deny All DDL & DML Commands In Sql Server

I want to deny all ddl and dml commands to AD and SQL Server users over all the databases on my database server. How can i do that?
2
votes
2answers
154 views

How to make DDL changes to large tables and cause minimum server and object contention?

I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database ...
7
votes
3answers
376 views

What events still occur within a transaction that gets rolled back?

Is it true that all data modifications are undone when the transaction they are a part of gets rolled back? For example if a cursor that just executed a stored procedure 100 times that updated a ...
8
votes
1answer
195 views

If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
3
votes
2answers
272 views

Migrate foreign key type from char to binary - ways to deal with the fallout?

I am in the process of slimming down a database that has been using char/varchar fields where binary/varbinary would be a better choice (given the data represented is byte arrays). One of the fields ...
0
votes
1answer
3k views

errno: 150 “Foreign key constraint is incorrectly formed” [closed]

CREATE TABLE IF NOT EXISTS `Register_Per` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `DOB` varchar(20) NOT NULL, `Gender` CHARACTER(8) NOT NULL, `ConNo` int(11) NOT NULL ...
0
votes
1answer
44 views

Strategy for making Postgres support multiple versions of client code

I'm about to embark on an overhaul of our code for pushing to Postgres, and want to solicit some feedback before going on. The setup here is that we've got a lot of software deployed with a non-...
1
vote
1answer
283 views

How to check and change function volatility in Postgres?

I have a function that I think is defined as volatile, but I now know I want it stable. How can I check the function volatility (volatile, stable, immutable) of a certain function? When a function ...
0
votes
1answer
342 views

Prepare statements plan invalidation in postgresql

Postgresql caches plans for prepared statements and their subsequent executes. see: https://www.postgresql.org/docs/current/sql-prepare.html https://stackoverflow.com/questions/7142335/how-does-...
0
votes
1answer
2k views

creating a new table results in incorrect syntax error [closed]

i'm trying to create a blank table which already exists in another SQL instance. so i just right clicked on the table and choose "SCRIPT CREATE TO" to export the structure to a query window, and it ...
9
votes
3answers
887 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 );...
-1
votes
2answers
9k views

Truncate all Tables in Database with T-SQL

Is there a way to truncate all tables in a database without using sp_MSForEachTable in TSQL Language? Background: not my call, DBA company policy does not allow sp_MSForEachTable. EXEC ...
2
votes
1answer
537 views

SQL Create Script DDL Table from View

How do I script a Create Table DDL for a View? Example View: create view dbo.CustomerTransaction as select cust.CustomerId, -- this is int cust.CustomerName, -- varchar(25) tr....
-1
votes
2answers
34 views

Upon deletion I want to set another value that is in the table (random) as the foreign Key, if i delete that foreign key, how can i do this?

For example, I have a database that has coaches and gymnastics, if a coach leaves the squad then every gymnast they are assigned too need to be randomly set a new coach. This needs to be done in SQL ...
1
vote
0answers
42 views

Noob Questions - Database approach

I have a database of "records" that relate to photos - title, location, text, tags, etc. I am now adding more fields for other media types, videos for example will get an transcript, audio will get ...
0
votes
1answer
145 views

How to trigger a function with DDL query?

I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored ...
1
vote
1answer
45 views

How to find maximum of average grouped by in sql

I have three tables Faculty {id, name}, Student {id, faculty_id, name}, Course_student {id, student_id, mark} And i would select top student in each faculty I'm tired to perform this in multiple ...
2
votes
1answer
634 views

Making a column unmodifiable/non-updatable after its row's initial creation?

What is the most idiomatic way to make a column unmodifiable/non-updatable after its initial creation via INSERT in Oracle? CREATE TABLE my_table ( id NUMBER NOT NULL CONSTRAINT ...
-2
votes
1answer
148 views

DROP and CREATE versus ALTER [closed]

QUESTIONS: Is is better to ALTER an object definition (i.e. function, stored procedure) in a production environment, rather than IF EXISTS (...) DROP (...) CREATE...? Does SQL Server treat the two ...
1
vote
1answer
640 views

Adding a CHECK constraint doesn't improve performance of ATTACH PARTITION on postgres table

I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote.... Before running the ATTACH PARTITION command, it is ...
4
votes
0answers
1k views

How do I rename a table with minimal downtime in a production postgres database?

I understand that renaming a table in postgres boils down to a simple catalog update. However, it also requires an ACCESS EXCLUSIVE lock to ensure transactions are safe. What would be the ideal way ...
12
votes
3answers
9k views

Why use master to create a database?

I have a short question, why do I use use master; to create a database? Here is the example from the Microsoft documentation USE master ; GO CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME =...
7
votes
2answers
338 views

Postgres Transaction OOM on 100k DDL statements

We execute approximately 100k DDL statements in a single transaction in PostgreSQL. During execution, the respective Postgres connection gradually increases on its memory usage and once it can't ...

1
2 3 4 5 6