Check Foreign Key integrity of existing data

Hi,
I'm in the process of writing a script that inserts or updates default data
for a database.
I came to the conclusion that I have to temporarily disable certain foreign
keys.
At the end of the script however, I'd like to check existing data to verify
that everything is still ok.
Basically something like this:
ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyForeignKey
-- Insert data here
ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
existing data, right?
-- Process other tables here
-- At this point I'd like to check that the FK_MyForeignKey constaint is
valid for existing data
Now I know that I could do something like this:
ALTER TABLE MyTable DROP CONSTRAINT MyForeignKey
ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT MyForeignKey ...
However, is there a way to check existing data without dropping and
re-adding the constraint?
Or is dropping and re-adding not that costly?
Thanks,
Erik> ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
> existing data, right?
Right.

> However, is there a way to check existing data without dropping and
> re-adding the constraint?
Use an IF EXISTS (or similar query)...test prior to running the ALTER
TABLE...CHECK statement.
HTH
Jerry
"ESPNSTI" <ESPNSTISPAM@.Hotmail.com> wrote in message
news:eGZJSnZ1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm in the process of writing a script that inserts or updates default
> data
> for a database.
> I came to the conclusion that I have to temporarily disable certain
> foreign
> keys.
> At the end of the script however, I'd like to check existing data to
> verify
> that everything is still ok.
> Basically something like this:
> ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyForeignKey
> -- Insert data here
> ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
> existing data, right?
> -- Process other tables here
> -- At this point I'd like to check that the FK_MyForeignKey constaint is
> valid for existing data
>
> Now I know that I could do something like this:
> ALTER TABLE MyTable DROP CONSTRAINT MyForeignKey
> ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT MyForeignKey ...
>
> However, is there a way to check existing data without dropping and
> re-adding the constraint?
> Or is dropping and re-adding not that costly?
> Thanks,
> Erik
>|||OK, so manually run a check.
Didn't even think of that. :)
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ufro3Za1FHA.1040@.TK2MSFTNGP14.phx.gbl...
check
> Right.
>
> Use an IF EXISTS (or similar query)...test prior to running the ALTER
> TABLE...CHECK statement.
> HTH
> Jerry|||You can also check out DBCC CHECKCONSTRAINTS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ESPNSTI" <ESPNSTISPAM@.Hotmail.com> wrote in message news:OVnT3Gb1FHA.1212@.TK2MSFTNGP10.phx
.gbl...
> OK, so manually run a check.
> Didn't even think of that. :)
> Thanks!
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ufro3Za1FHA.1040@.TK2MSFTNGP14.phx.gbl...
> check
>sql

Check constraint - SQL problem

Hi,

I am new to database development and am writing a database as part of a
university course

I have created a table as below called CableWire - the table is created ok.

CREATE TABLE CableWire
(CableWireID CHAR(7),
BSstandard CHAR(16),
Colour VARCHAR(16),
Material VARCHAR(16),
MetresInStock INTEGER,
PRIMARY KEY (CableWireID));

However when I try to alter the table by adding a CHECK constraint:

ALTER TABLE CableWire
ADD CHECK (MetresInStock >= 0);

I get a pop-up box: "Line: 21
SQLSTATE = 37000
[Microsoft][ODBC dBase Driver] Syntax error in field definition, Continue?"

(line 21 equated to the 2nd of those 2 lines). The syntax seems perfectly
acceptable to me. Any help appreciated.

Regards,

MaryHello, Mary

The syntax is perfectly acceptable in Microsoft SQL Server 2000, but
are you using SQL Server or dBase ? The error message indicates that
the ODBC dBase driver is involved. If you want this to run on dBase,
perhaps you should try your question on another newsgroup.

Razvan|||Some minor commetns about the design and some questions.

1) Why is every non-key column NULL-able?

2) I don't know the wirte business, so who defines the cablewire_id
codes? I know the ISO stuff for machine screws, etc.

3) Likewise, what is the BS Standard. My first guess was British
Standards, since you spelled color wrong :)

4) Don' t you use Pantone or Land color numbers? Can you give me an
example of CHAR(16) color name? I assume that it is a name, not a
code, but since you did not follow ISO-11179 rules, I don't know.

CREATE TABLE CableWire
(cablewire_id CHAR(7) NOT NULL PRIMARY KEY,
bs_standard CHAR(16 NOT NULL),
colour_name VARCHAR(16) NOT NULL,
material_type VARCHAR(16) NOT NULL,
stock_level INTEGER DEFAULT 0 NOT NULL
CHECK (stock_level >= 0));

Otherwise, your syntax was fine.|||Hi Mary,
I have just created table and added check constraint using alter
statement without any error on SQL SERVER 2000. Could you send more
detail about environment where you encountered this error.
Ash
http://www.astragalaxy.com

check Access table exists from SSIS

I am writing my first SSIS task. I have an MS Access database and I'm moving the contents of each table into a like-named SQL Server database. The task is working properly except for one step.

The source Access database may or may not have one of the tables in it. It will always have TableA, TableB, and TableC, but may or may not have TableD. I need to write my SSIS package in such a way that it will detect the presence/absence of TableD and either run or skip the import step for this table accordingly.

Can someone assist with the step that detects the table presence/absence in the MSAccess database?

thanks,

matt tag

easy.

I never work with Access/Excel, but can you run an Execute SQL task against it and populate an Integer variable with the result set?

This is the query. You'll need to map a parameter, or simply hard code the table name in place of the "?" below. If the output is greater than zero (which you can test in a precedence constraint) then the table exists, if not then the table does not exist:

SELECT count(*) as TableExists
FROM MSysObjects
WHERE MSysObjects.Type=1
and Name = ?|||

Matt,

The post of the Phil is excellent, but you must do it inside the control flow. Although you have a control error for the OLE DB Source that you can redirect to do something in the case of the source does not exist...

Regards,

Pedro