Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [ddl]

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

DDL is Data Definition Language, which is a general term used to refer to commands, scripts, or functions that are used to create the structures, objects, and framework of a database.

This can include table definitions, indexes, constraints, foreign key relationships, views, stored procedures, and functions.

In the most general sense, DDL can be any statement that begins wither either CREATE, DROP or ALTER.

DDL is related to but different from , or Data Manipulation Language, which is used on the actual data within the database objects.

296 questions
63
votes
2 answers

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
user32234
54
votes
2 answers

Best way to populate a new column in a large table?

We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL constraint to it). If I understand Postgres correctly…
Collin Peters
  • 765
  • 1
  • 6
  • 9
40
votes
2 answers

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy search. Can anyone tell me how I would check this?
Jeremy Holovacs
  • 1,271
  • 4
  • 20
  • 27
37
votes
2 answers

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 sys.objects, but how do I populate the ALTER TABLE…
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
33
votes
3 answers

Constraint to enforce "at least one" or "exactly one" in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address | is_active 1 | alice@bar.com | t 1 …
Kevin Burke
  • 855
  • 3
  • 9
  • 19
31
votes
9 answers

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 tables? Here are some example tables that you can…
sjdh
  • 767
  • 3
  • 8
  • 10
30
votes
3 answers

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 both columns. It's either or but one is a must.
Kevin
  • 447
  • 2
  • 5
  • 7
28
votes
3 answers

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 used wherever possible, and implies (without ever…
Mark Amery
  • 1,106
  • 3
  • 14
  • 24
27
votes
2 answers

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 alterations in a transaction locally (that was then…
sennett
  • 370
  • 4
  • 7
25
votes
1 answer

How to use transactions with SQL Server DDL?

I have a login table into which all inserts are done by a single stored procedure. CREATE TABLE dbo.LogTable( LogRefnr int IDENTITY(1, 1) NOT NULL, LogQuery varchar(255) NOT NULL, LogTime datetime NOT NULL, logQueryDuration int…
bernd_k
  • 12,369
  • 24
  • 79
  • 111
22
votes
4 answers

Why won't some DBMS's allow rollback for certain DDL statements?

Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow it.. Are there technical reasons for not supporting…
Joril
  • 441
  • 1
  • 5
  • 10
19
votes
2 answers

Disabling Checking of Schema On Function/Stored Procedure Creation

I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts to change the tables/columns/data. …
Brian Mains
  • 597
  • 2
  • 10
  • 17
18
votes
2 answers

How can I map a login to a database using T-SQL (not SSMS)

I'm writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part: I just want to do the equivalent of clicking the little box under "Map" for the msdb database and assigning that user to…
eddie_cat
  • 559
  • 2
  • 6
  • 11
18
votes
3 answers

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added them again. Now my id's go from 68 -> 92 and this…
Gijs
  • 333
  • 1
  • 2
  • 7
17
votes
5 answers

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to do it (assuming I could suffer even downtime - the…
Ran
  • 1,573
  • 9
  • 21
  • 35
1
2 3
…
19 20