Questions tagged [ddl]

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

Filter by
Sorted by
Tagged with
7
votes
3answers
293 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 ...
6
votes
1answer
163 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
248 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 ...
-1
votes
1answer
172 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
33 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-...
0
votes
0answers
28 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
36 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
91 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
98 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
547 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
2k 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
330 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
245 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
63 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
36 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
192 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
69 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
245 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
384 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
185 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
193 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 ...
4
votes
2answers
904 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
71 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
745 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
513 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
1k 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
309 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
51 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
79 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 ...
3
votes
2answers
9k 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
251 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 ...
6
votes
3answers
8k 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
801 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
2k 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
248 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
571 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
120 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
509 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, ...
6
votes
3answers
5k 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 ...
1
vote
1answer
392 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
5k 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
82 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 ...