Questions tagged [ddl]
Data Definition Language; defining database structures but not their contents.
282
questions
1
vote
1
answer
1k
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-...
- 183
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
0
votes
1
answer
439
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 ...
user129291
2
votes
1
answer
22k
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 ...
user129291
8
votes
1
answer
2k
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 ...
- 205
3
votes
1
answer
2k
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 ...
- 323
5
votes
1
answer
87
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....
- 1,019
5
votes
1
answer
641
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 ...
- 471
2
votes
1
answer
324
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 ...
- 1,490
0
votes
1
answer
682
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 ...
- 31
3
votes
0
answers
2k
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 ...
- 43
5
votes
1
answer
2k
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 ...
- 10.6k
-4
votes
3
answers
84
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 ...
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
1
vote
1
answer
854
views
Find current users DDL statement running, Oracle 11g
I have created a database trigger (oracle 11.2.0.4 standard edition on windows 2008 R2) to intercept any drop or truncate statements, eg
drop table abc123;
and that will raise an exception to ...
- 11
3
votes
2
answers
6k
views
Adding a nullable column concurrently with an update or insert
In SQL Server, when adding a column that allows nulls to a live SQL Server database with millions of rows, what happens if an insert or update is occurring at the same time as the DDL update?
For ...
- 31
2
votes
1
answer
958
views
Idempotent PostgreSQL schema dump that can be applied to an existing DB
I'm looking for a way to replicate a PostgreSQL schema to a database which may already have some of it defined. I wonder if there's a simple way to produce DDL that is idempotent (i.e. that can be ...
- 173
1
vote
2
answers
615
views
DDL-trigger for ALTER_AUTHORIZATION
I need to perform some audit, when change of a securable ownership, such as
ALTER AUTHORIZATION ON SCHEMA::[SchemaName] TO [PrincipalName];
happens in database.
Database scope DDL-trigger seems an ...
- 2,354
2
votes
1
answer
4k
views
Duplicate key value error message on a database for a writing groups scenario
I am trying to create a database as portrayed in the following diagram:
This is the SQL-DDL code I have written:
CREATE TABLE Publishers
(
PublisherName VARCHAR(50) NOT NULL,
...
- 31
3
votes
2
answers
1k
views
Always Encrypted - Out of Memory Exception when attempting to encrypt a column in a table
Using Always Encrypted we are attempting to encrypt one column of type varchar(50) in a table that contains 5.5 million rows with the deterministic encryption. After a while the wizard returns failure ...
- 679
10
votes
2
answers
3k
views
Clustered index creation fail on table creation
We've got an error when running the following script;
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Table_Name')
BEGIN
CREATE TABLE ...
- 588
2
votes
0
answers
851
views
mysql - Do DDL statements replicate under Row Based Replication if the Default Database is not set?
In Statement Based Replication the Default Database is of paramount importance.
e.g.
replicate-do-db=dbTwo
USE dbOne;
UPDATE dbTwo.tableTwo . . . .
will fail as dbOne has been set as the default ...
- 2,086
2
votes
1
answer
2k
views
dynamically create trigger to audit row changes
I'm using this approach to create a trigger to catch data changes. However, the system allows schema changes, and I would like to create this trigger dynamically after a new table has been created. I ...
- 123
6
votes
4
answers
3k
views
Transaction log is full changing column type in a large table
I've got a table of more than 400 million rows and want to convert the datatype of one of its columns, specifically datetime to datetime2(2).
If I execute my statement, I get the following error:
...
- 61
0
votes
1
answer
28
views
Appending IDs to duplicate descriptions for groups of data and single row data
I'm in need of some guidance with this problem I'm working on. First I'll need to explain the type of table I'm working with.
The table has a description field for each record. Each record has a ...
1
vote
1
answer
4k
views
Multiple foreign keys referencing a single table in one statement
I have written the following statement:
CREATE TABLE ORDEN (
`numero` INT UNSIGNED ZEROFILL NOT NULL,
`fecha` DATE NOT NULL,
`cliente` INT(6) UNSIGNED NOT NULL,
`nombre_cliente` ...
- 13
28
votes
3
answers
29k
views
Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?
Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is ...
- 1,075
0
votes
2
answers
526
views
What configurations affect the speed of DDL statements in PostgreSQL
I would like to setup PostgreSQL in such a way that dropping and recreating the entire database (~100 tables/views + a few rows per table) is as fast as possible.
This is for a development environment ...
- 211
1
vote
1
answer
314
views
Accesing a nested table column
If I have for example this table:
CREATE TABLE table1 OF TYPE type1
(
PRIMARY KEY (col1)
);
And a second table that containts the nested table:
CREATE TABLE table2
(
Col2 table1
);
How do ...
- 11
0
votes
1
answer
38
views
Understanding Function Index
Looking at Oracle's docs on the Function Index, what's the meaning of rivers in the following?
CREATE INDEX area_index ON rivers (area(geo));
SELECT id, geo, area(geo), desc
FROM rivers
...
- 495
1
vote
1
answer
262
views
Why will my ALTER and UPDATE not work in the same query in this specific instance?
I have a problem with a query where i am trying to ALTER a table by adding 3 columns to it, then updating each column with 1 specific piece of data.
I am aware there are several posts like this but ...
- 5
0
votes
1
answer
16k
views
Why am I getting PLS-00103 when I try to create a table in a PL/SQL procedure?
I am getting this error:
[Error] PLS-00103 (23: 9): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
...
- 171
2
votes
1
answer
374
views
Throttle DDL statement to avoid impacting performance of other queries
In MySQL, is there any way that I can make an ALTER TABLE or other DDL statement run more slowly, to mitigate its impact on the performance of other queries against the same database? I have found ...
- 1,075
0
votes
1
answer
799
views
how do I determine why I cannot drop a schema
A schema was created with the name of a windows user [DOMAIN\Foo]. I receive this error message when attempting to drop the schema.
DROP SCHEMA [DOMAIN\foo]
Msg 15151, Level 16, State 1, Line 8
...
- 721
1
vote
0
answers
54
views
MSSQL Deadlocking preventing DDL statement in different DB from running
I had a user doing a massive SQL data compare from server A to server B involving database X.
On Server B, I was trying to run a CREATE SYNONYM statement in database Y, which was being entirely ...
- 11
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
3
votes
3
answers
4k
views
Truncate temporary table commits transaction?
Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction?
...
- 453
0
votes
1
answer
71
views
MySQL Schema Design: One Possible Values Between Multiple Columns
I'm working on a schema for a MySQL database. I have tables which are derivatives of one another. For example, rather than a single "animals" table, I might have separate "mammals" and "birds" tables:
...
- 1
0
votes
2
answers
194
views
How to transfer DDL changes between different stages?
What technical solutions are available to track DDL changes and transfer them between different stages (e.g. DEV -> QA -> PROD) on postgreSQL databases? Is there a common mechanism to do that?
- 101
3
votes
2
answers
842
views
MySQL Online DDL Index Creation
I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is ...
- 71
2
votes
1
answer
2k
views
How can CREATE OR REPLACE fail with already exists?
DETAIL: Key (proname, proargtypes, pronamespace)=(rand, , 2200) already exists.: CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS
How on earth is that even possible? How can CREATE OR REPLACE ...
- 417
1
vote
1
answer
51
views
Changing Key (PK, FK) properties in DROP/Add column statements
I want to change a table by adding a column. So far nothing to see here, BUT I would like to make this column part of a composite key, i.e., I now have a layout
table_name( Field_1 datatype PK, ...
- 47
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
2
votes
1
answer
2k
views
MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index
It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, ...
- 121
1
vote
1
answer
2k
views
Change column from NOT LOGGED to LOGGED
I have a database DAT1 running on DB2 version 10.5.
I want to enable logging for all CLOB and BLOB columns of my table TAB1.
The table was initially created with those columns as NOT LOGGED but now I ...
- 125
1
vote
3
answers
3k
views
How to get the DDL for Oracle object without extra characters
When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script.
For example, if I create this sample ...
- 109
6
votes
1
answer
1k
views
Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table?
I understand that views are implemented using the rule system but I'm unclear if this has any advantages/disadvantages when running transactional DDL against them. Does issuing CREATE OR REPLACE VIEW ....
- 621
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
3
votes
2
answers
9k
views
SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints
I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is:
SQL Error: ORA-12991: column is reference ...
- 133
3
votes
1
answer
93
views
Create a table based on a table depending on the values in the initial table column
I have a table like:
+----+--------+----------------+
| id | name | address |
+----+--------+----------------+
| 1 | Ivan | Moscow,Moscow |
| 2 | Petr | Samara,Samara |
| 3 | Grigor ...
