What's the most efficient way to add DELETE constrain to a database

Hi all,

Database: SQL Server 2000 SP3a, front end application created with
Visual FoxPro 8 SP1.

To my surprise, I found out that somehow, *all of* my tables have lost
their Primary Keys and Foreign Key assignment. While the apps still runs
okay (because the Identity still intact, hence all insertions run as
they should be and I don't use any SPs to maintain business rules), I
understand clearly that this is something I *must* fix immediately.

I used to create the relationship and maintain DELETE constrain in Edit
Diagram window found on the MS Enterprise Manager. Since I have to
recreate all relationship and constrain all over again, it's going to
take some time if I use diagram.

QUESTION:
I know that instead of using diagram, I can use T-SQL statement via
Query Analyzer, can anybody give me a clue how to add the constrain?
There are about 90 tables on the database and I have to fix five
databases with the same problem (and the same structure).

TIA,
Willianto

Jack
Wed Jun 30 01:32:44 CDT 2004

On Wed, 30 Jun 2004 12:34:49 +0700, "Willianto"
<willianto@remove-me.telkom-and-me.net> wrote:

>Hi all,
>
>Database: SQL Server 2000 SP3a, front end application created with
>Visual FoxPro 8 SP1.
>
>To my surprise, I found out that somehow, *all of* my tables have lost
>their Primary Keys and Foreign Key assignment. While the apps still runs
>okay (because the Identity still intact, hence all insertions run as
>they should be and I don't use any SPs to maintain business rules), I
>understand clearly that this is something I *must* fix immediately.
>
>I used to create the relationship and maintain DELETE constrain in Edit
>Diagram window found on the MS Enterprise Manager. Since I have to
>recreate all relationship and constrain all over again, it's going to
>take some time if I use diagram.
>
>QUESTION:
>I know that instead of using diagram, I can use T-SQL statement via
>Query Analyzer, can anybody give me a clue how to add the constrain?
>There are about 90 tables on the database and I have to fix five
>databases with the same problem (and the same structure).

An example of adding a primary key is:

alter table xxx
add CONSTRAINT [xxxPK] PRIMARY KEY CLUSTERED (xuniq)

The full syntax is in the Transact-SQL Help.

Y
Wed Jun 30 01:33:36 CDT 2004

Willianto,

I am not sure whether this is the "most efficient way" but one alternative

For two tables do this through the SQL enterprise manager diagram window. In
this window there will be a "Save to a script file" button which will give
you the corresponding SQL script for all the actions you have done in the
Diagram window. (Note it may have some unnecessary code as well.) Now
knowing the commands that needs to be done, write a small VFP prgram that
can do the same SQL using SQLEXEC for your 90 tables.

Hope this helps.

Best Regards,
Y. Sivaram

"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
news:uoJZjNmXEHA.1888@TK2MSFTNGP11.phx.gbl...
> Hi all,
>
> Database: SQL Server 2000 SP3a, front end application created with
> Visual FoxPro 8 SP1.
>
> To my surprise, I found out that somehow, *all of* my tables have lost
> their Primary Keys and Foreign Key assignment. While the apps still runs
> okay (because the Identity still intact, hence all insertions run as
> they should be and I don't use any SPs to maintain business rules), I
> understand clearly that this is something I *must* fix immediately.
>
> I used to create the relationship and maintain DELETE constrain in Edit
> Diagram window found on the MS Enterprise Manager. Since I have to
> recreate all relationship and constrain all over again, it's going to
> take some time if I use diagram.
>
> QUESTION:
> I know that instead of using diagram, I can use T-SQL statement via
> Query Analyzer, can anybody give me a clue how to add the constrain?
> There are about 90 tables on the database and I have to fix five
> databases with the same problem (and the same structure).
>
> TIA,
> Willianto
>
>