RE: Transact SQL Question

Sun, 01 May 2005 17:49:25 -0700

Ahh, you were looking at a screen shot, okay, things finally make sense on my end.

 

Good luck on the test.

 

The Transcender Exams are excellent exam prep.

http://www.transcender.com/

 

- Daniel Elmore

 

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brent Helms
Sent: Sunday, May 01, 2005 7:48 PM
To: [email protected]
Subject: Re: Transact SQL Question

 

Thanks for the response.  I was wanting to start creating my tables by hand to perfect my TSQL skills in preparation for the Design and Implementation Test.  When I was creating constraints, I thought I may be doing something wrong or missing something.

----- Original Message -----

Sent: Friday, April 29, 2005 7:09 PM

Subject: RE: Transact SQL Question

 

The check data flag is not affected by whether or not there is data in the table. It’s a creation time process, meaning it’s only run when the DDL is executed, the flag is not stored in the DDL. Nor does it need to be, because it’s not going to be executed again. The check box is not suppose to stay checked, you check it, create the constraint and then leave it be.

 

 

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brent Helms
Sent: Friday, April 29, 2005 4:49 PM
To: [email protected]
Subject: RE: Transact SQL Question

 

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