Two ways to do this... Creating the constraints when creating the data model OR using SQL to use the 'reference' constraint. Does 2005 provide any other automated method of creating the Primary Key - to Foreign key constraints without writing the SQL to do this?
Thx
What do you mean by automated method? Using DDL is not automated enough? You could use SMO to create the constraints but you have to write VB.NET or C# code. You can also use the GUI but I wouldn't recommend that because it can mess up your tables depending on what you are doing. For example, it can drop and recreate tables depending on the changes you made in the diagram designer. And this may or may not be the right thing to do. Often using SQL DDL is the most direct and optimized way to make schema changes.
Another option is to use a modelling tool like Visio or ErWin or Power Designer. Those have capabilities to generate scripts to create the schema and/or make changes. Note that the features supported by the tools may differ from the database engine. For example, using included columns or indexing computed columns will be a problem. Similarly, the tools might allow defining cascading foreign key relationships even if there are cycles for example but if you try to apply those changes in SQL Server it will fail because the database engine lacks supports for those. On the other hand, if you are trying the same against Oracle it will succeed. So if you have complex schema relationships then you will have to use a hybrid approach. Or if you support different database engines you will have to tailor the scripts to the specific engine. Visio for example allows you to define additional scripts in the model that can be executed againt the database engine.
|||Thank you for your answer, I didn't know that the GUI would do the things that you described. That was what I was referring to. I guess for me, the automated method would be that when you create a primary key, the create the foreign key in the 'create table' GUI, the foreign key constraint would be automatically created if the column names are the same. Does this make sense? Maybe coffee is the answer.
As you mentioned above, there are products that do provide this automation, I just wondered if the 2005 engine had included this feature.
Thx
|||katgreen777 wrote:
the create the foreign key in the 'create table' GUI, the foreign key constraint would be automatically created if the column names are the same. Does this make sense?
It makes sense to some extent. But you can't just go by column names. You could have "Description" column in multiple tables which have no relation to each other except for the fact that they represent some sort of description of an entity. This rule might however work for key attributes.
I don't know if SSMS diagram designer or VS designer infers these things from the model. You have to ask in the Tools or Visual Studio forum. Data modelling tools however does this for you depending on how you create the model. For example, if you drag and drop a key column from one table to another it will establish a relationship that will translate to referential constraint on the database. Of course, none of this comes free. You typically pay a lot for designer/modelling tools.
|||One note on this... isn't it a rule of thumb not to include computed columns in a table?
thx
|||I don't know of any such rule. There are cases where using computed column is useful. But it is a proprietary feature anyway so for data modelling purpose probably you shouldn't worry about it. If you hit some design issues in SQL Server due to limitations you can use computed columns & indexes on computed columns.|||The books I've been reading say that you shouldn't include computed columns in a table because of the extra space they take up, but instead, create a view where the computations take place. That was what I was referring to.
Kat
|||Wow! Which books are those? You need to throw them in the garbage. Computed column as the name implies is computed at run-time. There are ways to persist computed column values in SQL Server 2005 which will take up space or if you index computed columns. Otherwise, it is no different than defining a view with the same expression. Even with view the notion that computations take place within a view is incorrect. The view definition is parsed into the statement (SELECT or DML), compiled, optimized and executed.|||Yes, you aren't the first person to say that about my books, any good ones you would recommend?
Kat
