.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Scripting Foreign Keys in SMO

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
 

I am scripting out our database using SMO.

Our database is contained in a schema. When I script out the foreign keys, the resulting script is missing the schema-qualifier on the REFERENCES clause.

My code looks like:

ScriptingOptions
 oScriptingOptions 
=
 
new
 
ScriptingOptions
();

        oScriptingOptions
. FileName = strFilePath ;
        oScriptingOptions
. AppendToFile = true ;
        oScriptingOptions
. DriForeignKeys = true ;
        oScriptingOptions
. SchemaQualify = true ;
        oScriptingOptions
. SchemaQualifyForeignKeysReferences = true ;
        oScri


View Complete Post


More Related Resource Links

Listing Foreign Keys

  
If I run the following code (quote many times on the internet), which should show all foreign keys in a database, but it only returns a partial list:   select CCU.table_name src_table, CCU.constraint_name src_constraint, CCU.column_name src_col, KCU.table_name target_table, KCU.column_name target_col, RC.UPDATE_RULE, RC.DELETE_RULE from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU where CCU.constraint_name = RC.constraint_name and RC.UNIQUE_CONSTRAINT_NAME = KCU.CONSTRAINT_NAME order by CCU.table_name, CCU.constraint_name   If I run the following, removing the KEY_COLUMN_USAGE link, it returns the full list:   select CCU.table_name src_table, CCU.constraint_name src_constraint, CCU.column_name src_col, RC.UNIQUE_CONSTRAINT_NAME, RC.CONSTRAINT_NAME, RC.UPDATE_RULE, RC.DELETE_RULE from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC where CCU.constraint_name = RC.constraint_name order by CCU.table_name, CCU.constraint_name   I can't figure out what the problem is, the ones that are being left out of the first query ad the ones where the unique_constraint is set to the column name, not the constraint's name. For example, the first one appears in both query results, the second one only appears in the secod queries results:   src_table src_constr

Foreign & Primary Keys

  

I have the following FK on Items table

ALTER TABLE [dbo].[items]  WITH NOCHECK ADD  CONSTRAINT [FK_items_i_tmpnam] FOREIGN KEY([i_tmpnm_id])

REFERENCES [dbo].[i_tmpnam] ([tmpnam_id])

NOT FOR REPLICATION 

GO

Yet I'm able to delete all records from i_tmpnam and leave orphans in the FK field in the Items table. So, does NOCHECK disables the constraint completely?

 


Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

multiple foreign keys to same table and same column

  

I am working with two Tables. Table A contains Id, Names. Table B  keeps only the reference to table A by InventoryID1, InventoryID12, InventoryID3.

Table A
-----------
InventoryID
Name
Price

Table B
---------
MenuID
Name
InventoryID1
InventoryID2
InventoryID3

I need a select statement to pull up all items from table B, showing the InventoryID 1, 2, 3 by their corresponding Name on Table A  

At first I thought this would be a simple inner join statement, which it does work when only InventoryID1 is involved
 
select * from Inventory
inner join Menu On (Inventory.InventoryId1 = Menu.Inventory)

The problem arises when I try to obtain the Inventory Name from Table A for the second or third (InventoryID2 or InventoryID3) from Table B. So basically, my problem is how to request a second or third Inventory.Name in the same select statement


Processing orphan dimension records with NULL parent foreign keys

  
<head> <style type="text/css"> .style1 { font-family: Arial, Helvetica, sans-serif; } .style2 { font-family: Arial, Helvetica, sans-serif; font-size: x-small; margin-bottom: 0px; } .style3 { font-size: x-small; } .style4 { font-family: Arial, Helvetica, sans-serif; font-size: x-small; margin-bottom: 0; } .style5 { color: #0000FF; } .style6 { font-family: "Courier New", Courier, monospace; } .style7 { color: #0066CC; font-weight: bold; font-style: italic; } </style> </head>

This link Handling Data Integrity Issues in Analysis Services 2005 is a classic paper on handling data integrity. The snowflaked data structure decribed in it is:

Product Class (dimension)  <--  Product (dimension)  <--  Sales (fact) where some Products do not have a Product Class

My situation is similar, but a bit more complex.   I don't actually have referential integrity problems . . . orphans are valid.

I have two fact tables TableA and TableB. They are related in a [0-or-1] to a [0-or-many] structure.

  • That is, each TableA record may have zero, one, or many TableB records associated with

SQL Server Mgmt Studio - primary/foreign keys

  

I currently have a number of tables in my database, which include these 2 tables:

tblOne - Uid(pk), FirstName, LastName, Email

tblTwo - HCCID(pk), Uid, Category, Description, Company, Manager

 

In my application I have users from tblOne logging in and creating records which are inserted into tblTwo. Thankfully this is working ok, however, my problem is with the Uid in tblTwo (fk).

When a new record is created in tblTwo, the HCCID(pk) iterates, so i have a numeric identifier for each record. However is there any way i can also keep a record of the user by recording the Uid in tblTwo based on which user creates the record?

Seeing as though this is already a pk in tblOne and I also already have a pk in tblTwo, is this possible?

Any ideas will be greatly appreciated!


When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys

  
When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys replicated?
lcerni

Problem in displaying values for foreign keys in Dynamic data asp.net web app

  

I have the following tables in my database which are as given under:

Table 1: CREATE TABLE [dbo].[PromotionDiscountValues]( [PromotionID] [int] IDENTITY(1,1) NOT NULL, [DiscountAmount] [int] NOT NULL, CONSTRAINT [PK_PromotionDiscountValues] PRIMARY KEY CLUSTERED ( [PromotionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Table 2: CREATE TABLE [dbo].[PromotionCodes]( [PromotionCode] nchar NOT NULL, [CustomerPromotionDiscountID] [int] NOT NULL, [EmployeePromotionDiscountID] [int] NOT NULL, [IsActive] [bit] NOT NULL, CONSTRAINT [PK_PromotionCodes_1] PRIMARY KEY CLUSTERED ( [PromotionCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

There is a foreign key relationship between columns (1) PromotionID of table PromotionDiscountValues and CustomerPromotionDiscountID of table PromotionCodes and (2) PromotionID of table PromotionDiscountValues and EmployeePromotionDiscountID of table PromotionCodes

When i browse the PromotionCodes table in the dynamic data listing, the following columns get displayed:

  1. PromotionCode

  2. IsActive

  3. Prom

Denormalising dimension tables with multiple foreign keys

  

In SSAS 2008 I am trying to "denormalise" 2 source dimension tables into a single dimension.   The main table has 2 FKs to the other  parent table which I am trying to merge in.   What I have done so far is to make 2 copies of the parent table attributes in the dimension and name them accordingly.   Then I set the attribute relationships to link each FK column to the appropriate set of parent columns.   i.e. ending up with something like this:

DIM KEY -> DIM FK1 -> FK1 parent columns

             -> DIM FK2 -> FK2 parent columns

I thought that would be enough for SSAS to realise that FK1 parent columns come via the table relationship involving FK1 and FK2 parent columns come via the table relationship involving FK2.

I was wrong!   When I look at the SQL generated when processing the dimension it seems to choose one of the FKs and uses the same FK when joining both times to the parent table.   i.e. something like:

SELECT DIM.FK1, parent.parent columns from DIM, parent where DIM.FK2 = parent.KEY

Is it possible to do what I am trying??


Surrogate vs Natural Primary Keys - Data Modeling Mistake 2 of 10

  
In case you're new to the series I've compiled a list of ten data modeling mistakes that I see over and over that I'm tackling one by one. I'll be speaking about these topics at the upcoming IASA conference in October, so I'm hoping to generate some discussion to at least confirm I have well founded arguments.

The last post in this series Referential Integrity was probably less controversial than this one. After all, who can argue against enforcing referential integrity? But as obvious as surrogate keys may be to some, there is a good deal of diversity of opinion as evidenced by the fact that people continue to not use them.

Visual Studio Shortcut Keys

  
The following tables describe the shortcut keys in the Microsoft Dynamics AX Debugger.

Cutting Edge: Explore Rich Client Scripting With jQuery, Part 2

  

Achieving cross-browser compatibility for events is no easy task. The jQuery event handling API addresses the differences in event handling across browsers, allowing you to write more predictable JavaScript.

Dino Esposito

MSDN Magazine April 2009


Cutting Edge: Explore Rich Client Scripting With jQuery, Part 1

  

Thanks to selectors and function chaining, jQuery allows you to write compact, cross-browser code.

Dino Esposito

MSDN Magazine March 2009


The XML Files: XSLT Keys, Select vs. Match, Conflict Resolution, and More

  

This month Aaron Skonnard covers how keys work in XSLT, XSLT match and select attributes, SelectNodes order, finding