Questions tagged [ddl]
Data Definition Language; defining database structures but not their contents.
282
questions
1
vote
1
answer
723
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 ...
- 2,086
1
vote
0
answers
524
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 ...
- 113
1
vote
1
answer
811
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?
- 69
2
votes
2
answers
433
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 ...
- 35k
7
votes
3
answers
732
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 ...
- 35k
8
votes
1
answer
347
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 ...
- 35k
3
votes
2
answers
312
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 ...
- 334
0
votes
1
answer
6k
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 ...
- 13
0
votes
1
answer
243
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-...
- 859
1
vote
1
answer
950
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
1
answer
820
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-...
- 127
0
votes
1
answer
3k
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 ...
- 21
9
votes
3
answers
1k
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 );...
- 62.1k
0
votes
2
answers
18k
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 ...
user175631
2
votes
1
answer
807
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....
user172334
-1
votes
2
answers
38
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
0
answers
46
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 ...
- 11
1
vote
1
answer
292
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 ...
- 111
1
vote
1
answer
198
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 ...
3
votes
1
answer
1k
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 ...
user68575
-2
votes
1
answer
359
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 ...
- 555
2
votes
1
answer
2k
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 ...
- 1,125
8
votes
0
answers
3k
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 ...
- 181
12
votes
3
answers
16k
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 =...
- 123
8
votes
2
answers
603
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 ...
- 299
0
votes
1
answer
44
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' ...
- 1
0
votes
1
answer
900
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 ...
- 1,933
13
votes
2
answers
9k
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
, ...
- 131
1
vote
1
answer
503
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 ...
- 408
3
votes
2
answers
3k
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:
...
- 5,957
0
votes
2
answers
1k
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 ...
- 197
3
votes
1
answer
4k
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:
...
- 55
0
votes
2
answers
1k
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 ...
- 109
0
votes
1
answer
235
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 ...
- 149
0
votes
1
answer
1k
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,342
13
votes
2
answers
97k
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 ...
- 149
4
votes
2
answers
300
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,
...
- 62.1k
5
votes
3
answers
8k
views
How to prevent asking for password when creating new database in PostgreSQL 10?
I use the following batch file to create a new database in PostgreSQL 10.
echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
createdb -h localhost -p 5432 -U postgres myDB
But it asks to enter ...
- 199
1
vote
4
answers
114
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 ...
- 71
4
votes
2
answers
4k
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 ...
- 143
15
votes
3
answers
33k
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 ...
- 255
1
vote
2
answers
2k
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 ...
- 115
3
votes
3
answers
10k
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 ...
- 141
4
votes
1
answer
899
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
3
answers
8k
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 ...
user129291
6
votes
1
answer
2k
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;
- 61
6
votes
1
answer
248
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,301
1
vote
1
answer
2k
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 ...
- 217
1
vote
1
answer
5k
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, ...
26
votes
3
answers
19k
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 ...
- 407