Showing posts with label dependencies. Show all posts
Showing posts with label dependencies. Show all posts

Tuesday, March 20, 2012

Circular dependencies from constraints

Hey!

I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files.

To achieve this ive created te following tables (truncated for clearity):

###################
# Sites #
###################
# ID [Int] #
# ... #
###################

###################
# Folders #
###################
# ID [Int] #
# SiteID [Int] #
# FolderID [Int] #
# ... #
###################

###################
# Files #
###################
# ID [Int] #
# SiteID [Int] #
# FolderID [Int] #
# ... #
###################

Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both!

Then i set up the foreign constraints as follows:
Folders.FolderD -> Folders.ID
Folders.SiteID -> Sites.ID
Files.FolderID -> Folders.ID
Files.SiteID -> Sites.ID

All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible)

Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?

Hi Supermajs,

Firslty, I would remove the constraints.

Secondly, remove the SiteID and FolderID fields from the Folders and Files tables.

Thirdly, add the fields ParentID and ParentTypeID to the Folders and Files tables.

Then add a new table called tblParentType with the following records: -

ParentTypeID ParentType

1 Site

2 Folder

Then add a constraint between ParentType.ParentTypeID and Folders.ParentTypeID and between ParentTypeID.ParentTypeID and Files.ParentTypeID.

Now a record within Folders will look like this: -

ID ParentID ParentTypeID

1 3 1 this folder is a folder under site id 3

2 14 2 this folder is a folder under folder id 14

The same applies for the Files table.

By doing it this way, you can easily add new levels without needing extra fields that always contain NULL.

For example, you could add a new layer above site called server. The ParentType table would be as follows: -

ParentTypeID ParentType

1 Server

2 Site

3 Folder

|||

.In relational modeling it is files and association, I think you have designed flat files that could be a problem because DRI(declarative referential integrity) comes with restrictions. If you have 50 files that are associated they belong together that is how you can start with 100 files and end up with 5 tables. Try the Normalization tutorial and free data models to clean up your design and post again so I can help you with the DRI Cascade operation because it comes with fixed requirement of if A references B then B must exist. Hope this helps

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

http://www.databaseanswers.org/data_models/

Thursday, February 16, 2012

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 serverI 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" <dharts@.yahoo.com> wrote in message news:8ce6b687.0308281418.12df77dc@.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|||John Bell (jbellnewsposts@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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

Checking columns level dependencies

Hi all,
Need a big favour, I have to collect information like
TableName-> it's all columnNames--> dependent table name of EachColumn(where this column is refrenced)

I have managed to get some info like:-

select so.name as 'Table Name',sc.name as 'Column Name',st.[name], sc.length,sc.isnullable,so.crdate
from syscolumns sc
inner join sysobjects so on so.id=sc.id
inner join systypes st on st.xtype= sc.xtype
where so.xtype='U'
order by so.name

sp_depends and sp_MSdependencies is not returning me same fields..I would start with sysobjects, syscolumns and sysreferences. Try the query and if you get stuck, let us know