I am currently creating a master ddl for our database. Historically we have used backup/restore to version our database, and not maintained any ddl scripts. The schema is quite large. My current thinking: Break script into parts (possibly in separate scripts): table creation add indexes add triggers add constraints Each script wo...
I've got a program that periodically updates its database schema. Sometimes, one of the DDL statements might fail and if it does, I want to roll back all the changes. I wrap the update in a transaction like so: BEGIN TRAN; CREATE TABLE A (PKey int NOT NULL IDENTITY, NewFieldKey int NULL, CONSTRAINT PK_A PRIMARY KEY (PKey)); CREATE IN...
Hello. I'm using NHibernate 2 and PostgreSQL in my project. SchemaExport class does a great job generating DDL scheme for database, but it's great until the first application. Is there any way to generate "migration" DLL (batch of "ALTER TABLE"'s instead of DROP/CREATE pair) using NHibernate mapping files? ...
I want to setup a mechanism for tracking DB schema changes, such the one described in this answer: For every change you make to the database, you write a new migration. Migrations typically have two methods: an "up" method in which the changes are applied and a "down" method in which the changes are undone. A single comma...
I'm trying to create a table with two columns comprising the primary key in MySQL, but I can't figure out the syntax. I understand single-column PKs, but the syntax isn't the same to create a primary key with two columns. ...
What is the syntax to drop a column in a MySQL table, if that column exists on version 4.0.18? ...
I've been trying to find a free database creator for mac os, and i'm not being able to find any. Anyone know of a free one i could download? EDIT: I need that the application generate the sql (mysql in this case) also :) ty ...
I am developing a website using SQL Server Express on my development machine. My web hosting company is providing me with SQL Server 2005. At the moment all I have is a database that I develop with and a database that is on the live server. I do not have the original scripts to generate the schema but I can auto generate the create scri...
I am trying to write a tool that can compare a database’s schema to the SQL in an install script. Getting the information from the database is pretty straightforward but I am having a little trouble parsing the install scripts. I have played with a few of the parsers that show up on Google but they seemed somewhat incomplete. Ideall...
Suppose I create a table in Postgresql with a comment on a column: create table t1 ( c1 varchar(10) ); comment on column t1.c1 is 'foo'; Some time later, I decide to add another column: alter table t1 add column c2 varchar(20); I want to look up the comment contents of the first column, and associate with the new column: select...
I've been using Visio 2002/2003 Enterprise Architect to do my database schema design visually and then forward-generate the DDL to create the database. I wanted to switch to Visio 2007, but while it does have database diagramming support, it doesn't have the ability to generate DDL. Bummer. I am really disappointed because it seems li...
OK, so Sybase (12.5.4) will let me do the following to DROP a table if it already exists: IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'a_table' AND type = 'U' ) DROP TABLE a_table GO But if I try to do the same with table creation, I always get warned that the table already exists, because it went ahead and tried...
I'm looking at the MySQL docs here and trying to sort out the distinction between FOREIGN KEYs and CONSTRAINTs. I thought an FK was a constraint, but the docs seem to talk about them like they're separate things. The syntax for creating an FK is (in part)... [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) R...
Do any .NET frameworks allow you to create Business Entities first then Database. In other words allow you to use DDD / Persistence Ignorance method of backing into the database later. Any tools that allow the Models/Classes you have written to generate the SQL DDL and migration scripts. Feel free to rework my verbiage, and make it a be...
I have two tables, we'll call them Foo and Bar, with a one to many relationship where Foo is the parent of Bar. Foo's primary key is an integer automatically generated with a sequence. Since Bar is fully dependent on Foo how would I setup the primary key of Bar given the following constraints: Records for Bar are programatically gene...
I have four tables (A,B,C,D) where A is the parent of one to many relationships with B and C. C and D are parents to a one to many relationship with table D. Conceptually, the primary keys of these tables could be: A: Aid B: Aid, bnum (with foreign key to A) C: Aid, cnum (with foreign key to A) D: Aid, bnum, cnum (with foreign keys ...
Given the schema: MACHINE_TYPE { machine_type } MACHINE { machine, machine_type } SORT_PLAN { sort_plan, machine_type } SCHEDULE { day_of_week, machine, sort_plan } and the business rule: A sort plan can be assigned to any machine of the same machine_type. How do I enforce that, in SCHEDULE, the tuples referenced by machine a...
I am currently running into a problem when trying to import an Oracle 9i SQL script into my local Oracle 10g Express database. I am trying to import the DDL from the 9i database to the 10g express database. I keep getting "Not compatible - Your export file is not supported". Has someone been able to get this working? Please let me know w...
This is the issue, when I define the ddl (drop down list or select box) I don't know the selected value. When a user edits a row, the user can select an item from the list. But the selected item isn't set. I want to set the selected item when the user clicks a button to edit the row. The proper way, I think, is to get the ddl that was ...
Does anyone know of a reasonably priced tool that will create DDL statements to create a SQL Server database and appropriate Insert statements to recreate the data? I use the Red Gate tools to do database compares (including content compares) and this comes close (I could always compare with an empty schema) but I was wondering if there...