Relationships problem in Sql Server 2000

I am using Enterprise Manager for Sql Server 2000. In designing a foreign key relationship for a table, let's call it table A, I have more then one column acting as a foreign key for the same primary column in another table, let's call it table B. While I can specify for one of those foreign key columns in table A, which has the same column in another table as its primary key, to Cascade Deletes and Updates, successfully, when I try to also specify Cascade Delete and Updates for a second of my foreign key columns, which has the same column in another table as its primary key, I get an error saying that the second relationship with the Cascade "may cause cycles or multiple cascade paths."
I do not understand why this should be happening. If the Cascade is allowed for one of my columns, why does allowing it for more than one of my columns which point to the same primary key column cause this problem. Or is this just a bug in Sql Server 2000 which has been fixed in later releases of the product.No, it's not a bug, it's by design. If you want to create more than one cascade relationship between two tables, then there is only a way is to specify only first relationship as cascade and maintain other(s) via trigger(s).|||I do not understand the reasoning behind this design. I do not see how it matters if deleting a row in one table causes more than one row in another table to be deleted because of more than one cascade relationship between tables. Also the same situation for updated cascades should work properly also if there is more than one relationship between two tables. I can understand if the relationship goes both ways it could cause a recurring cycle, but if the relationship just goes one way it never can cause cascading problems AFAICS.

relationship question

Hi It may be a stupid question but I can not see why I define a relationship
and disable the option "Disabling a Foreign Key Constraint with INSERT and
UPDATE Statements"
in this case I just dont define the relationship.
Somebody can say a comment.
You can define FK on tables on simply disable them for loading
maintainance or other purposes, and then enable them again. Otherwise
you would have to drop them and recreate them.
CREATE TABLE SomeParentTable
(
ParentPKCol INT
CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
)
Create table SomeChildTable
(
PKChildCol INT,
ParentPKCol INT NOT NULL
CONSTRAINT fk1_SomeParentTable
FOREIGN KEY
REFERENCES SomeParentTable (ParentPKCol)
)
--Doesn=B4t work, CHECK is enabled
insert into SomeChildTable Values (1,1)
--Disabling the CHECK
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--This works now
insert into SomeChildTable Values (1,1)
--Delete it once again
DELETE FROM SomeChildTable
--THis is the normal behaviour
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--Inserting first the parent then the child records
insert into SomeParentTable Values (1)
insert into SomeChildTable Values (1,1)
Drop table SomeChildTable
Drop table SomeParentTable
HTH, Jens Suessmeyer.
|||Ohh absolutly, now I see the reason
Kenny M.
"Jens" wrote:

> You can define FK on tables on simply disable them for loading
> maintainance or other purposes, and then enable them again. Otherwise
> you would have to drop them and recreate them.
>
> CREATE TABLE SomeParentTable
> (
> ParentPKCol INT
> CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
> )
> Create table SomeChildTable
> (
> PKChildCol INT,
> ParentPKCol INT NOT NULL
> CONSTRAINT fk1_SomeParentTable
> FOREIGN KEY
> REFERENCES SomeParentTable (ParentPKCol)
> )
>
> --Doesn′t work, CHECK is enabled
> insert into SomeChildTable Values (1,1)
> --Disabling the CHECK
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --This works now
> insert into SomeChildTable Values (1,1)
> --Delete it once again
> DELETE FROM SomeChildTable
> --THis is the normal behaviour
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --Inserting first the parent then the child records
> insert into SomeParentTable Values (1)
> insert into SomeChildTable Values (1,1)
>
> Drop table SomeChildTable
> Drop table SomeParentTable
>
> HTH, Jens Suessmeyer.
>
sql

Relations between tables - contraints diagram

Hi all,
I have a big problem. I have many tables with constraints, with foreign keys. I need to create a ordered list of tables, on the top must be the basic table what has no parents, then the second level tables (those depends on the first level) the the names of third level etc.

for example:
Table A[id]
Table B[id, idc]
Table C[id, ida]
Table D[id, ida]
Table E[id, idc]

I tried it by using information_scheme but I was unsuccesfull.

The result should be:
A
C
D
B
E

Thank you,
Tom.There isn't always a relational solution to this problem, because users sometimes create non-linear (aka graphical) relations. I know... Bad user! Naughty user! No donut!

I worked up an iterative solution, but I don't have it handy. If no one else posts a solution (you still have a copy of that one Rudy?), I'll dig it up and post it, but it might be a day or three.

-PatP|||thank Pat,
I apreciate your help, I am also working now on a iterative solution, but it does not work. So if you will have a time I will be very glad to see your solution and help, three days or five, does not matter.
Thanks again.

Tom.

Relational databases

I have a membership database and a profile database with a userid column in both. The foreign key is in the profile database where i want a persons profile details to go like location,occupation and what not. How do I make it so when a new user registers a userId is created in the profile database that matched the userid in the membership database so i can query out profile details based on a registered members userid?

There's a couple of ways -

- Add a separate column for the membership id to the profile.

- Make the UserID in the Profile non-auto generated and set it when you create the record as under

INSERT INTO MembershipTable .....DECLARE @.UserID INTSET @.UserID = SCOPE_IDENTITY() -- gets the identity of the lastnew record-- create the profile recordINSERT INTO ProfileTable (UserId, .... VALUES(@.UserID, ....

Related tables with different publications

My database contains tables related together by foreign keys (Not for
Replication), and I want to design a transactional replication with queued
updating option between them, can I put each related table on a different
publication?
Saeed,
the general recommendation is to have the related articles in the same
publication, to ensure the same processing order is applied at the
subscriber. Is there any particular reason you want to split the publication
into two?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
our database is too huge, we have tables with millions of records, so if any
problem occured to any table or adding new column(s), we just want to start
reinitilize the effected table. not all related tables to minimize the
efforts on both the database and the network.
Thanks
"Paul Ibison" wrote:

> Saeed,
> the general recommendation is to have the related articles in the same
> publication, to ensure the same processing order is applied at the
> subscriber. Is there any particular reason you want to split the publication
> into two?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||OK - then you could have different publications and ensure
that they synchronize in the correct order.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Related Tables in Model Files

I want to create a model file that relates two tables. However, these tables do not have a foreign key relationship in the database. Is there any way to do this? I'm hoping to programmatically generate the XML for the smdl file. Here's the XML that is generated when there is a foreign key:

<Role ID="G958db767-3d65-4445-9ccb-5f76c41720fb">

<Name>Linked Problem</Name>

<RelatedRoleID>Gf5496c9e-13b8-4c66-965d-c3a3c9a78cc8</RelatedRoleID>

<Cardinality>OptionalOne</Cardinality>

<Relation Name="dbo_Incident_FK_Incident_Problem" RelationEnd="Target" />

</Role>

Is there any way to change this to XML reference a table and join fields instead of a FK?

The Role in the report model must be bound to a Relation in the Data Source View (DSV), but the Relation does not need to have any corresponding FK constraint in the underlying database. The solution is to define the Relation you want in the DSV section of the file, then create the Role pair and bind them to it.

|||Hi Bob,

To form the relation, I assume you mean that I make a key/keyref pair in the schema. Is this correct?

As for the role pair, I don't understand exactly where these items go. I looked at the ones generated when there was a FK in the database. It looks like one goes at the end of the first table's attribute (field) list and the second one replaces the attribute in the second table. Is this correct?

If you have any XML samples I would really appreciate it. I tried everything I could think of today and got nothing but errors when I tried to upload the model files.

Thanks!|||Never mind. I've since discovered that you can set up a foreign key with the "NO CHECK" option. I think I can do that and save myself from having to programmatically generate SMDL. Thanks!

Related tables

Is it possible to retrieve all tables that a given one is related to
via foreign keys?Try this:

SELECT DISTINCT o1.name FROM sysreferences r
INNER JOIN sysobjects o1 ON o1.id=r.fkeyid
INNER JOIN sysobjects o2 ON o2.id=r.rkeyid
WHERE o2.name='YourTable'
/*
UNION
SELECT DISTINCT o2.name FROM sysreferences r
INNER JOIN sysobjects o1 ON o1.id=r.fkeyid
INNER JOIN sysobjects o2 ON o2.id=r.rkeyid
WHERE o1.name='YourTable'
*/

Razvan|||Razvan Socol wrote:
> Try this:
> SELECT DISTINCT o1.name FROM sysreferences r
> INNER JOIN sysobjects o1 ON o1.id=r.fkeyid
> INNER JOIN sysobjects o2 ON o2.id=r.rkeyid
> WHERE o2.name='YourTable'
> /*
> UNION
> SELECT DISTINCT o2.name FROM sysreferences r
> INNER JOIN sysobjects o1 ON o1.id=r.fkeyid
> INNER JOIN sysobjects o2 ON o2.id=r.rkeyid
> WHERE o1.name='YourTable'
> */

Thanks, this gets me closer to the goal. By the way, if I try it as it
is, it returns no rows at first. When I remove the WHERE clause, then
it works!

I will need to look more into the system tables.