Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [ddl]

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

1
vote
1answer
26 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
54 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
47 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
35 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
46 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
2k 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 =...
6
votes
2answers
104 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
25 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
21 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
75 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
17 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
98 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
47 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
118 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
287 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
55 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
30 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
38 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
366 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
240 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
101 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 ...
2
votes
2answers
1k 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 ...
0
votes
0answers
64 views

get_ddl Returns ENABLE ON QUERY COMPUTATION for 11g Materialized Views

I am hoping someone can help us solve an issue that is causing errors in 12c (12.2.0.1.0) during our regression testing. We have recently migrated from 11g to 12c so that none of our materialized ...
2
votes
3answers
3k 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 ...
0
votes
2answers
234 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
631 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
88 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
656 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
332 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
97 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
304 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
688 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, ...
0
votes
0answers
141 views

Table creation on Ubuntu insanely slow?

Using MySQL 5.7.17 for OSX, I have a test suite that runs in between 1 and 1.5 minutes. The tests use fixtures so there is a lot of DDL activity with each test case. When this same test suite is run ...
2
votes
2answers
2k views

Constraint - 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
262 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
196 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
2k 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
73 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
2k 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
410 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
372 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 ...
5
votes
1answer
80 views

When and who shrunk the ldf file?

I’m trying to find information about when and who shrunk a ldf file but I can't find the information anywhere. Can someone point me in the right direction? Microsoft SQL Server 2008 R2 (SP2) - 10....
3
votes
1answer
110 views

PostgreSQL event trigger to force extension installations into particular schema

I have a dedicated schema extensions (PostgreSQL v 9.6.3) where I intend to install all extensions for my DB. I would like to somehow disallow (or at least remind) users installing extensions without ...
2
votes
1answer
135 views

Always On DDL operations

Always On availability group with two nodes, synchronous commit. Redo thread contention on the secondary replica regularly creates a very large redo queue. I have confirmed the wait types are similar ...
0
votes
0answers
18 views

Sybase Database : Except sa_role which role can extract ddl from sybase database [duplicate]

I am currently using Sybase Central 15.x. In which I have a database, a login and a User. Here I need to give permission(s)/role(s) to a user by which it able to extract ddl of database. There I found ...
0
votes
1answer
238 views

Sybase Database : least permission/roles to extract ddl

I am currently using Sybase Central 15.x. In which I have a database, a login and a User. Here I need to give permission(s)/role(s) to a user by which it able to extract ddl of database. There I found ...
1
vote
0answers
497 views

Serialized Inventory Tracking

I'm developing a inventory system with optional serial no tracking (which users can turn on for items they select). in order to keep stock records currently i came up with two options. Option 01 use ...
5
votes
1answer
355 views

Prevent DROP COLUMN in PostgreSQL

Can I stop DROP COLUMN from succeeding on particular tables in PostgreSQL 9 or 10 or later? My application makes some assumptions about columns being forever, not disappearing. I'm afraid of later ...
-4
votes
3answers
64 views

Not able to create this table [closed]

I am trying to create this table in my database with no luck at all. All my tables look the same, but this one is giving me trouble. I am using Notepad to type, then copying the code to the MySQL 5.7 ...
8
votes
3answers
827 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 ...