Questions tagged [ddl]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
23 views

Single metadata base table in Postgres

I am a fan of adding timestamps to tables in the form of created_at, updated_at and deleted_at. But it can be tedious to add these fields to every table. I was wondering, is it a bad design decision ...
0
votes
1answer
18 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
73 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
44 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
528 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
1k 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
258 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
31 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
36 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
0answers
163 views

ORA-00955 Object name already exists when it actually doesn't

I'm using PowerDesigner to model my database. I'm creating tablespaces and setting the correct properties to columns, tables, indexes, etc. (at least I think so). With PowerDesigner, I can then ...
0
votes
1answer
52 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
33 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
140 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
63 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
183 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 ...
2
votes
0answers
298 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
4k 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
160 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
35 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
93 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 ...
3
votes
1answer
579 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
55 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
582 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
74 views

Update data from one table to another in sql server

I have a log table where accidentally one date column got updated with current date for 9 Million records. Luckily I had a backup table, so now I want to populate data from backup table to current ...
0
votes
2answers
437 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
886 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
222 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
47 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
61 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 ...
2
votes
2answers
5k views

How to use default value of data type as column default?

How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago: CREATE TABLE test ( col_1 CHAR(12) NOT NULL, col_2 INTEGER NOT NULL ...
4
votes
2answers
246 views

Advantages to column constraints over table constraints

Is there any advantages whatsoever, other than style to writing this, CREATE TABLE foo ( a int PRIMARY KEY, b int ); And then, CREATE TABLE bar ( a int REFERENCES foo, c int ); Over, ...
1
vote
4answers
102 views

How to model a limited history of a table

Context I have 3 entities (user, shop and car), a car can only have one regNum, one shopId and one ownerId at a time, that's why they are embedded in the car table. create table "user" ( id ...
3
votes
2answers
2k views

Create stored procedure inside try-catch block

I'm using sqlserver 2014 and trying to create a StoredProcedure inside try-catch block like this: BEGIN TRY CREATE PROCEDURE [ammeghezi1] (@w INT) AS SELECT '' END TRY BEGIN CATCH ... END ...
4
votes
3answers
7k 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 ...
1
vote
2answers
666 views

Oracle, Is there a way to request a lock with alter table drop partition?

We have an Oracle RAC cluster with about 100 tables that continually receive data. All tables have a timestamp field and the data is partitioned by date. Stopping the receipt of the data is not an ...
2
votes
3answers
1k views

How long can this ALTER TABLE statement take? [closed]

Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL Suppose there are 45 GB data space and 2GB index space; about 3 million rows in this table; column YYY is varchar(8000) now and can be ...
4
votes
1answer
220 views

Retrieve All MySQL/MariaDB Table Definition From .frm Files

I wonder if it is possible to retrieve the MySQL/MariaDB table definitions from the .frm files? For example, if I wanted to steal your database and I had access to your MySQL/MariaDB files but didn't ...
1
vote
3answers
2k 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 ...
6
votes
1answer
518 views

Disable multiple logins in one step

Is there a way to disable multiple sql logins based on a set of users in a table? For example: ALTER LOGIN (Select userid from tbl_user) DISABLE;
6
votes
1answer
114 views

Can I specify a name for the index which SQL Server creates for my unique constraint?

In SQL Server I deploy a table with a UNIQUE constraint: CREATE TABLE [dbo].[Something] ( [IdentityId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID() -- Whatever else... ) and this makes ...
1
vote
1answer
445 views

Translating ERD diagrams into SQL DDL statements

I am currently establishing a database for Students and Courses using ERD. The style of ERD I am using is similar to the diagram below from the ERD Diagram Question: Note: The picture above is NOT ...
1
vote
1answer
2k views

LAST_DDL_TIME Not Changing after compilation of package body [closed]

On Nov 24 th, we created the package body. Again today we recompiled it, but last DDL date has not changed. Is there any reason for this? Below is the query I'm using SELECT object_name, ...
4
votes
3answers
4k views

Create a Constraint such that only one of two fields must be filled

I have a table in SQL server where the users need to enter data in either of two columns. That is, One of the two must have data inputted but at the same time i don't want to allow users to input in ...
0
votes
1answer
393 views

How to Alter and add a suffix “DO_NOT_DROP” to several tables using PL/SQL LOOP

Say I have table A(see below) which has details of all the tables that has to be renamed. Also, what if the table does not exist and how to handle exception and find out how many of the tables were ...
1
vote
1answer
322 views

How to add constraint on a varchar column to have at least one non numeric character

I am trying to implement some constraints on tables so that there is no tampering of the data from what is expected. One of the constraints is on a varchar column. It should at least contain one non-...
5
votes
2answers
4k 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 ...
0
votes
1answer
81 views

SQL Server Concurrent Insert/Update and Storing Data

From: SQL Server 2014 Concurrent input issue Follow up question: Table Orderline stores information we receive from shops. OrderLineID is the unique, primary key in the table, since orders have ...
1
vote
1answer
5k views

If Exists before Update or Delete?

Before I write an update statement, should I check if it exists? Someone told me to do this So it avoids uselessly writing to the transaction log for value that already exists, And only takes a ...
7
votes
1answer
636 views

Changing the datatype of the primary key columns of partitioned tables

I am currently blessed with the following situation: We have a production database that is partitioned and uses primary keys of type int Multiple (~20) tables that each contain > 100.000.000 rows, 2 ...
2
votes
1answer
563 views

Replication and table locks during alter table

Recently we have noticed that a number of our Rails migrations end up deadlocking / freezing our app+DB in production. Preliminary investigation reveals that this is probably due to concurrent access ...