Not a bug
SQL generation for selected objects excludes references unless table is also selected
Publisher Service 3 years ago • updated 3 years ago • 3
Lets say I am adding new tables that have relationships to existing tables. When I select new tables and new relationships and use "selected only" option in SQL generation it doesn't generate foreign keys between new tables and existing tables (even though that relationship is specifically selected). The only workaround is to select existing tables as well and then remove DDL for existing tables from the script, which is cumbersome.
Customer support service by UserEcho
SQL script generation tool you've trying to use is "table" oriented. It will generate script that contains table and than other related object (references, indexes). General rule is: generated script must be runable against the real empty database. If you select a reference it will generate reference and corresponding tables.
If you want to create a script that applies changes between model versions. Please right click on the model name on documents page and pick "Model migration" option.
Unless you support branching of the model that solution doesn't work in real world with concurrent development. Let me give you an example:
Let's say I have model with tables A,B,C (version 1). I am working on a big feature and add tables D,E,F with relationship between D->A (version 2). That feature is not ready to go to production. In the meantime we have a small feature to add a column to table A (version 3) and that is rolled out to production. Then we add some more columns to table D and drop table F (version 4) and then finally ready to rollout that feature.
Using "model migration" feature there is no way to get correct SQL for the big feature changes. I can get DDL for version 1->2 and 3->4 but that will include adding then dropping table, instead of just having create table DDL for tables D,E
And that is just a simple example.