473,385 Members | 1,528 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Checking for Primary Key Dependencies

Anybody know if there is a system function that can be used from a
stored procedure that determines if a given primary key has existing
dependencies? I want to make a check for this and if there are none, I
will delete the record. If there are, I will change a field called
bitStatus from 1 to 0. Enterprise Mgr. does something like this under
All Tasks, Display Dependencies. The normal way I do it is to manually
check for the existance of the primary key in every dependent table.

SQL 2000 server
Jul 20 '05 #1
3 8075
I don't know about dependancies, but iof you use
EXEC sp_primarykeys (check books for parameter)
This will give you a list of the PK
--
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector - www.ciquery.com/tools/sqldirector/


"Dan Hartshorn" <dh****@yahoo.com> wrote in message news:8c**************************@posting.google.c om...
Anybody know if there is a system function that can be used from a
stored procedure that determines if a given primary key has existing
dependencies? I want to make a check for this and if there are none, I
will delete the record. If there are, I will change a field called
bitStatus from 1 to 0. Enterprise Mgr. does something like this under
All Tasks, Display Dependencies. The normal way I do it is to manually
check for the existance of the primary key in every dependent table.

SQL 2000 server


Jul 20 '05 #2
John Bell (jb************@hotmail.com) writes:
How about sp_depends?

If you want different output look at the source in master.


Not sure what you are thinking of, but it does not seem to me that
SQL Server saves any of this kind of information on sysdepends. It
saves CHECK constraints, but not FOREIGN KEY constraints. Presumably,
because CHECK constraints have code, while FK constraints have not.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Dan,

It sounds like you are asking this:

How can I find out if a particular primary key value
exists in the foreign key column of any dependent table?

Or for example, does customer number #324989 have any
entries in the Orders table, the BadChecks table, or the
ReturnedMerchandise table?

The dependencies displayed in Enterprise Manager are
dependencies of database objects (tables, columns,
views, etc.), not of single rows of data, so I don't think
sp_depends or Enterprise Manager is going to have anything.

And I don't think there is simple function to do this,
but there are solutions to the problem. This
is something like a garbage collection problem for
pointers in C - how do we know we've deleted the last pointer
to allocated data?

In the context of PK/FK relationships, you could
keep a master count of referring rows. Keep an integer,
in the main table - not just a bit, and that count will
be at zero when there are no references. It would have to
be updated by triggers on the dependent tables.

Let us know if this is what you are asking about.

-- Steve Kass
-- Drew University
-- Ref: 5D4089BF-0B8A-4D45-B820-923A2D6886EE
Dan Hartshorn wrote:
Anybody know if there is a system function that can be used from a
stored procedure that determines if a given primary key has existing
dependencies? I want to make a check for this and if there are none, I
will delete the record. If there are, I will change a field called
bitStatus from 1 to 0. Enterprise Mgr. does something like this under
All Tasks, Display Dependencies. The normal way I do it is to manually
check for the existance of the primary key in every dependent table.

SQL 2000 server


Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Dave Robinson | last post by:
I was wondering if anyone could help me with a problem I'm having. I've been using Dreamweaver to create a hotel booking system for a friend of mine, using MySQL (version 4.0.21) and PHP 5. The...
3
by: Moosebumps | last post by:
I'm wondering what kind of checking I can do on a python program before running it (besides reading over every line). I hate running a long python script, only to have it fail at the end because I...
8
by: Chango V. | last post by:
Hello, We seem to need a PIA for MSXML 4. According to the documentation, only the original publisher of a COM type library is supposed to create a .NET PIA. As far as I know, Microsoft hasn't...
3
by: Rich | last post by:
I have a form with 2 check boxes. One of the check boxes is used to specify that the user is a "primary contact." When I check the primary contact box I want a second box for "standard contact"...
6
by: Joey Lee | last post by:
Hi, I was wondering which is the best way of checking if a record exist in the database before inserting. There are quite a few ways I have seen. Here are some... 1> Insert and hit a...
8
by: Brendan | last post by:
There must be an easy way to do this: For classes that contain very simple data tables, I like to do something like this: class Things(Object): def __init__(self, x, y, z): #assert that x,...
0
by: Etienne Marcel | last post by:
Hello, I would like to know if there is something in distutils (or in Python stdlib) which allow me to check for dependencies (shared libraries, headers or any other progs) before...
1
by: =?Utf-8?B?S2VpdGggRHVQb250?= | last post by:
I have created a setup project for a forms application that I will be deploying in the near future. I have added the primary output to the setup project and all dependencies have been correctly...
4
by: Bernhard Merkle | last post by:
Hi there, think %Subject says all. I am wondering if there is some tool to check dependencies within python programs. (something like jdepend for python ;-) Of course the dependencies are...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.