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

learn more… | top users | synonyms

1
vote
1answer
48 views

Is there a way to add a column to a table without affecting performance?

How do you add a column to a table in production without affecting its performance? Or is this impossible? I'm asking about Postgres.
3
votes
3answers
37 views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is ...
3
votes
1answer
60 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
0
votes
1answer
53 views

How to store a version sequence per product

I need to create a table like the following one: +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 ...
1
vote
2answers
46 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
3
votes
1answer
36 views

Circular dependency in schema or boolean with CHECK?

Let's say I have the following tables: create table repositories ( id serial primary key, name varchar(255) not null ); create table remotes ( id serial primary key, url varchar(255) ...
1
vote
1answer
20 views

I really can't get rid of these conditional statements in mysqldump?

My mysqldump DDL file is filled with tons of this: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`... I've searched to no avail for an option to silence this. ...
-2
votes
1answer
49 views

Generate create table DDL from any arbitrary select query? [closed]

Is there a way, given any arbitrary select query like: select myfavoritenum, myfavoritetxt from tablewithmanycolumns and magically receive a create table statement: create table justmyfavorites ( ...
0
votes
1answer
31 views

IS-A relation when defining tables

I already asked this question on SO, prior to realizing that there may have been a more suitable place. I am trying to implement an IS-A relationship between tables. Say I have a singer and a band ...
0
votes
0answers
32 views

Slave executing queries with different charset

I need to change a database charset because of this issue and I solved my issue in staging environment with the SQL command ALTER TABLE `articles` CONVERT TO CHARACTER SET utf8 COLLATE ...
0
votes
1answer
34 views

What does a CONSTRAINT have to do with my unique index?

I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
0
votes
2answers
57 views

Transaction has set autocommit = 0, but still the update happens before I commit

I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of ...
0
votes
1answer
53 views

All users are having NO Privileges in phpMyAdmin

By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one ...
0
votes
1answer
78 views

How to quickly increase column size in Oracle?

In our Oracle 11 database we have a table with more that 100 million rows and we would like to increase size of one column (not used in any index). I tried to execute command alter table shipment ...
0
votes
2answers
84 views

Oracle SQL Creating Copy of Table Using DBMS_metadata

so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this ...
0
votes
1answer
129 views

How to track only DDL statements executed in MySQL

Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements. I had gone through the ...
6
votes
1answer
179 views

Transactional DDL workflow for MySQL

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database ...
3
votes
1answer
164 views

How do I drop all constraints from all tables?

I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from ...
1
vote
2answers
73 views

Best approach to maintain a single set of DDLs and DMLs for multiple database type

In our application, we support multiple types of databases namely Oracle, IBM DB2 (both UDB and ZOS), postgresql and So on. Currently we are maintaining separate DDLs and DMLs for each of these ...
0
votes
1answer
70 views

Alter trigger Oracle

Does alter trigger hang if a table is locked by some DML operation ? What kind of exception does it throw? Resource busy 0054 or some other exception...? I have tried to reproduce this scenario but ...
1
vote
1answer
211 views

SQL Server 2012: Give rights to read ddl but not modify?

What rights would I need to be give a user for them to be able to view all DDL code (such as stored procedure code) but not be able to edit any of it? The only possibility seems to be DDLadmin, but ...
1
vote
1answer
162 views

Add a column with Not Null [duplicate]

I have a existing table in which i am adding a column as NgoId whose datatype is int. I want to add as NOT NULL to the column . When I'm adding this I am getting the following error: Unable to ...
1
vote
0answers
44 views

Is that create table statement correct? [closed]

Although i am not part of this community for long time, i already feel like you are my family, since thats what family does. We help each other. I am thankful to everyone who helped me! CREATE TABLE ...
0
votes
1answer
31 views

Preview the increment in a Mysql table size before running the ALTER TABLE ADD COLUMN statement

Is it possible to preview how much the table size will be incremented by an ALTER TABLE ADD COLUMN statement before running the statement in the database?
2
votes
1answer
392 views

What means Command column in sp_who2 and text column in sys.dm_exec_sql_text?

I have a script that shows commands being fired in SQL Server 2008 database as follows: --shows who is active and executed commands declare @SPID int=null, @Status varchar(max) = null, @LOGIN ...
2
votes
2answers
232 views

Error on DDL statement never invokes CATCH

When I try to close the SS Management Studio window below is the error I understand that DDL statements are implicit COMMIT and the below WARNING message is because BEGIN TRAN never COMMIT or ...
2
votes
0answers
109 views

Cannot drop a Postgres table

I want to drop a few tables in my postgresql database. I am able to drop a few tables but there are 3 tables which I am unable to drop. I need to drop a few tables in my Postgres DB. I tried the ...
0
votes
1answer
125 views

Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber'

Yesterday I had run a migration script, produced by Visual Studio 2008 schema compare option, on a SQL Server 2008 database. It had run correctly. But today, when I executed the same script, it threw ...
1
vote
0answers
52 views

SQL Server Comparison

I am merging two different versions of a database. I want to add some specific fields from source database to destination, so I used Visual Studio to do that. It does the job quite well but only table ...
0
votes
1answer
28 views

Ensuring that 1 row has 5 specific records referencing it

Context: a TEST has many CASEs. Typical. However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5). I could do something like this: create table TEST ( ID ...
1
vote
1answer
42 views

What's wrong with my DDL statement? [closed]

I am trying to run: CREATE TABLE `members` ( `id` int(11) NOT NULL auto_increment, `users` text NOT NULL, `password` text NOT NULL, `ip` text NOT NULL, `status`text NOT NULL, UNIQUE KEY `id` (`id`) ) ...
0
votes
0answers
84 views

exporting DDL of all views Oracle

How can I go about exporting DDL of all my schema views in single .sql file ? I've read there is a function DBMS_METADATA.GET_DDL But I can't figure out how to use it for my case. I know how to ...
2
votes
3answers
4k views

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two ...
1
vote
1answer
791 views

Check constraint references another column in same table

I have this table but check constraint is throwing me an error: Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'. I've seen this as allowed ...
1
vote
1answer
49 views

Change column without interrupting service

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
5
votes
1answer
163 views

Why does dropping a MVIEW trigger a full refresh?

I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again. The MVIEW was created with a statement like this: create materialized view ...
0
votes
2answers
116 views

Modifying a part (one procedure) in oracle database package

Is it possible to modify an individual procedure inside an Oracle DB Package? instead of recreating (modification is not done using a SQL editor, so cannot modify a place and easily recompile the ...
0
votes
0answers
17 views

Managing schema migrations in Mondrian

Managing schema changes across dev, stg, prd is something we do well in our home-grown solution today. Specifically, at least one downstream team consumes our star schema. They're considering ...
2
votes
1answer
223 views

LOCK=NONE when adding NOT NULL constraint fails with “cannot silently convert NULL values” error despite no NULL values being present

I'm trying to use MySQL's online DDL feature to add a NOT NULL constraint to an InnoDB table without blocking writes to the table. According to manual, this should be possible - the linked table ...
1
vote
1answer
130 views

Can I run DDL in pretend mode in Oracle

I have a script to drop several objects in the database (including queues) -- when it partially fails I need to recreate the objects that have already been dropped to continue testing the script. For ...
1
vote
1answer
705 views

Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

What's a good way to easily convert a clustered index on a primary key column to a nonclustered index? I know how to do this manually, but it's time consuming if there are several tables which have ...
1
vote
1answer
204 views

Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint

I am importing from the GUI a CSV file into a table. I have created the following table: CREATE TABLE jira_tickets.daqa_rpt_tbl ( daqa_report_id bigint NOT NULL DEFAULT ...
2
votes
2answers
319 views

What is the advantage of using numeric primary keys in a relational database?

Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to): create table widgets ( widget_id number ...
0
votes
1answer
3k views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
3
votes
1answer
264 views

What happens when you create the primary key of a table on a different filegroup?

Creating a table on a different filegroup is absolutely no problem when using Sql Server. create table common.test ( id int, CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON ...
2
votes
1answer
590 views

Permissions to see and script objects, but not alter them?

I am a software developer. I have a production database that I need full ddl read access to, but not any ddl write or execute access. Is there a script I can send my DBA that will grant me full ddl ...
1
vote
1answer
1k views

How to generate create table script without primary and foreign key in Oracle

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well. SELECT ...
0
votes
0answers
42 views

Organizing a couple tables in postgresql

I have a data structure that is represented like this: { 'question_id': 1, 'is_required': False, 'question_text': [ { 'id': 1, ...
1
vote
1answer
76 views

What's the complexity of renaming a column in MySql?

We want to rename a column in our MySql (version 5.5) database with something like this: ALTER TABLE t1 CHANGE a b INTEGER; Is this an O(N) (N=number of rows in the table) or an O(1) operation? I ...
2
votes
1answer
133 views

Does a DDL in Oracle affect other transactions?

I understand that DDL statements in Oracle have an implicit commit before and after the statement, which obviously affects the current transaction. However, does a DDL statement in any way affect ...