RE: Transact SQL Question

Fri, 29 Apr 2005 14:52:48 -0700

I have been a little confused by your responses, so I had to re-read a few times. You said:

"Your wanting to create these foreign key constraits without checking the current keys in the table against their primary keys? If so, you need.... "

No, I do not want to do that as you are correct in stating that it could cause problems; however, I think I may have my answer now.  Initially, I reviewed a pre-existing diagram with the "Check Existing Data On Creation", and it was checked, so I ignorantly thought "Well gee, i better have this checked on my new constraint."

Since I am creating these tables from scratch, the "Check Data on Creation" will never be checked since there is no data to check against.  This is only valid when there is data already in existence in the table. Constraints implemented on tables without data do not have to worry about this I guess.  Does this make sense?  I guess Microsoft just didnt' want to check this option off if you create the table with the constraint at the same time; however, I bet if you create the constraint on a pre-existing table with an "ALTER" statement, and the table has data in it, I bet it will be checked with teh same script I just wrote.  I've yet to test that theory.

I really appreciate your feedback though, I think there was just a little bit of confusion.

Brent Helms
Carlot.com

Return-Path: <[EMAIL PROTECTED]> Fri Apr 29 16:55:41 2005
Received: from smtp.imailbox.com [207.168.61.195] by mail31.safesecureweb.com with SMTP;
Fri, 29 Apr 2005 16:55:41 -0400
Received: from nospam.imailbox.com (unverified [207.168.61.211]) by imailbox.com
(Rockliffe SMTPRA 5.2.5) with SMTP id <[EMAIL PROTECTED]> for <[email protected]>;
Fri, 29 Apr 2005 16:04:20 -0500
Received: from mx10.imailbox.com ([207.168.61.213])
by nospam.imailbox.com (8.12.11/8.12.11) with ESMTP id j3TKwKGf025352
for <[email protected]>; Fri, 29 Apr 2005 15:58:20 -0500
Received: from medworking.com (mail.medworking.com [64.5.36.39])
by mx10.imailbox.com (Postfix) with ESMTP id E6920450
for <[email protected]>; Fri, 29 Apr 2005 19:27:37 -0500 (CDT)
Received: from planform2 [71.96.91.241] by medworking.com with ESMTP
(SMTPD32-8.15) id ACAE1F0062; Fri, 29 Apr 2005 15:44:30 -0500
From: "Daniel Elmore" <[EMAIL PROTECTED]>
To: <[email protected]>
Subject: RE: Transact SQL Question
Date: Fri, 29 Apr 2005 15:58:06 -0500
Message-ID: <[EMAIL PROTECTED]>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0178_01C54CD4.3C315190"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
In-Reply-To: <[EMAIL PROTECTED]>
X-imailbox.com-MailScanner-Information: http://www.imailbox.com/support.cfm for more info...
X-imailbox.com-MailScanner: Found to be clean
X-MailScanner-From: [EMAIL PROTECTED]
Reply-To: [email protected]
Sender: [EMAIL PROTECTED]
X-SmarterMail-Spam: SPF_None
X-Rcpt-To: <[EMAIL PROTECTED]>

"Check Existing Data On Connection"  there is no such thing.
 
You mean:
 
"Check Existing Data On Creation"  which is what I justed explained to you.
 
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Brent Helms
Sent: Friday, April 29, 2005 3:33 PM
To: [email protected]
Subject: re: SPAM-LOW: RE: Transact SQL Question

No.  I want "Check Existing Data On Connection" to be checked, but the TSQL I have written does not enable this feature.  I verified that it was not checked when i went to my diagram and viewed the properties of this relationship.  I want to know what I have to modify in my script to make sure this option is checked.  So far, everything i have tried still leaves this option unchecked.

Brent Helms
Carlot.com

Return-Path: <[EMAIL PROTECTED]> Fri Apr 29 16:15:08 2005
Received: from smtp.imailbox.com [207.168.61.195] by mail31.safesecureweb.com with SMTP;
Fri, 29 Apr 2005 16:15:08 -0400
Received: from nospam.imailbox.com (unverified [207.168.61.211]) by imailbox.com
(Rockliffe SMTPRA 5.2.5) with SMTP id <[EMAIL PROTECTED]> for <[email protected]>;
Fri, 29 Apr 2005 15:23:48 -0500
Received: from mx10.imailbox.com ([207.168.61.213])
by nospam.imailbox.com (8.12.11/8.12.11) with ESMTP id j3TKHmYV020423
for <[email protected]>; Fri, 29 Apr 2005 15:17:48 -0500
Received: from medworking.com (mail.medworking.com [64.5.36.39])
by mx10.imailbox.com (Postfix) with ESMTP id 632F93D2
for <[email protected]>; Fri, 29 Apr 2005 18:47:05 -0500 (CDT)
Received: from planform2 [71.96.91.241] by medworking.com with ESMTP
(SMTPD32-8.15) id A32F480060; Fri, 29 Apr 2005 15:03:59 -0500
From: "Daniel Elmore" <[EMAIL PROTECTED]>
To: <[email protected]>
Subject: SPAM-LOW: RE: Transact SQL Question
Date: Fri, 29 Apr 2005 15:17:35 -0500
Message-ID: <[EMAIL PROTECTED]>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_016D_01C54CCE.92F3EAC0"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
In-Reply-To: <[EMAIL PROTECTED]>
X-imailbox.com-MailScanner-Information: http://www.imailbox.com/support.cfm for more info...
X-imailbox.com-MailScanner: Found to be clean
X-MailScanner-From: [EMAIL PROTECTED]
Reply-To: [email protected]
Sender: [EMAIL PROTECTED]
X-SmarterMail-Spam: BAYESIAN FILTERING, SPF_None
X-Rcpt-To: <[EMAIL PROTECTED]>

Your wanting to create these foreign key constraits without checking the current keys in the table against their primary keys? If so, you need to use the WITH NOCHECK flag. However this is _not_ a recommend practice. If you later do an update to one of those rows, the constrait will fire and the update will fail.
 
- Daniel Elmore
 
 

 -----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Brent Helms
Sent: Friday, April 29, 2005 3:00 PM
To: [email protected]
Subject: Transact SQL Question
I am having a tough time finding out how to manually write out the TSQL to generate a junction table that contains the following integrity constraint: "Check Existing Data On Connection". 

This is what I have so far:

CREATE TABLE sec_ApplicationModuleAccess
(
 ApplicationID INT NOT NULL,
 ApplicationModuleID INT NOT NULL
 CONSTRAINT sec_ApplicationModuleAccess_fk FOREIGN KEY (ApplicationID)
 REFERENCES sec_Applications (ApplicationID),
 CONSTRAINT sec_ApplicationModuleAccess_fk2 FOREIGN KEY (ApplicationModuleID)
 REFERENCES sec_ApplicationModules (ApplicationModuleID),
 CONSTRAINT sec_ApplicationModuleAccess_pk PRIMARY KEY CLUSTERED(ApplicationID,ApplicationModuleID)
)

The problem is: It does not set "Check Existing Data On Connection" to true (or checked).  It does set "enforce relationship for replication, inserts, and updates to true. 

Any ideas what the missing link is?

Regards,

Brent Helms
Carlot.com


Reply via email to