Questions tagged [ddl]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
40 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
129 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
31 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
45 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
27 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
40 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
22 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
39 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
13 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
22 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
43 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
357 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
19 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
46 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
111 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
74 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
153 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
105 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
330 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
184 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
264 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
2k 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
39 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
195 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
226 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
902 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
739 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
7k 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
462 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
33 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
39 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
116 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
42 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
485 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
114 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
469 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 ...
3
votes
0answers
983 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
7k 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
283 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 ...
0
votes
1answer
38 views

Error Code 1064, Cant figure this out [closed]

CREATE TABLE 'mydb.nation' ( 'natcode' CHAR(3), 'natname' VARCHAR(45) NULL, 'exchange_rate' DECIMAL(9,5) NULL, PRIMARY KEY ('natcode')); CREATE TABLE 'mydb.stock' ( 'stckcode' CHAR(3), 'stckname' ...
0
votes
1answer
452 views

Migrating Oracle Views to SQL Server

In the process of retiring an Oracle 11g host for an HRIS database, I've been asked to migrate as many assets as possible to a SQL Server 2016 instance for read-only archival. The volume of table ...
6
votes
2answers
3k 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 , ...
1
vote
1answer
125 views

How to explain/introspect what locks a DDL operation will need (Postgres)

I would like to have some way of inspecting what locks a DDL operation will need to acquire. Preferably something generic enough that I don't need to personally know/encode knowledge of all of the ...
2
votes
2answers
1k views

Can one make MySQL cluster on a unique index rather than a primary key?

The MySQL docs states that: When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. But that's not the only possibility, you can cluster off of a unique index instead: ...
0
votes
2answers
810 views

Oracle - Extract user DDL for migration

I need to export and import data from a Oracle 9i to Oracle 12.2 database. This old app doesn't log in as one privileged user - it logs in for each user. I have hundreds of users with nothing but ...
3
votes
1answer
2k views

Convert varchar column to inet

I have a table column containing IP addresses, datatype is varchar(255). I want to switch to inet datatype now. Is there a way to convert existing addresses in IP column to inet datatype? Tried: ...
0
votes
2answers
723 views

Can a table have multiple editioning views in Oracle RDBMS 11

Can a table ABC have multiple editioning views in Oracle Database? I have an editioning view ABC (created from table ABC) that shows the old edition to be OLD_EDITION_1 as its edition_name in the ...
0
votes
1answer
87 views

DDL for PostgreSQL for DB2 tables (migration)

How would I create the DDL for PostgreSQL for the following DB2 tables? It should be a one-to-one migration, so I would like to change only as little as possible at the schema (I have difficulties ...
0
votes
1answer
197 views

Table check contraint to verify unique on specific value

Oracle 10g: Suppose the following table (T_REGISTER): ID_PROCESS PERIOD CUSTOMER STATUS =========================================== 0001234 201801 12300344 INVALID 0001236 ...

1
2 3 4 5 6