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

learn more… | top users | synonyms

2
votes
1answer
109 views

Does PostgreSQL support online schema modification (DDL)?

Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table. Does PostgreSQL support this to any level? That is, can I add/drop columns from a table ...
1
vote
1answer
54 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
44 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
71 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
59 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
55 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
39 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
57 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
35 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
38 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
61 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
61 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
99 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
112 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 ...
1
vote
1answer
170 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 ...
9
votes
1answer
224 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
378 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
75 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
79 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
251 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
271 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
33 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
422 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
267 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
124 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
141 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
43 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
121 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
855 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
53 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
175 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
138 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 ...
2
votes
1answer
236 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
136 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
770 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
212 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
336 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
305 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 ...
3
votes
1answer
705 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
2k 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
45 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
79 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
145 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 ...