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.
Showing posts with label relationship. Show all posts
Showing posts with label relationship. Show all posts
Friday, March 30, 2012
relationships problem
VB.NET 05 (ADO.NET) / SQL SERVER 05.
i have a many-to-many relationship. therefore, according to the normalisation theory, i introduced a new table which acts as a link between them.
i have a many-to-many relationship. therefore, according to the normalisation theory, i introduced a new table which acts as a link between them.
1) tblOrders
PK: orderID
2) tblOrdersItems
FK: orderID
FK: itemID
3) tblItems
PK: itemID
the user wishes to view the details of a particular order which should include all the items
contained withing that particular order.
question: what is the technique to be used in order to get this done?
i) dim varOrderID as integer
ii) select tblOrdersItems.itemID where orderID = varOrderID
iii) HOW TO NOW FETCH the details of the items contained in this order from tblItems which should be displayed on a datagrid?
I believe you could use query similar to
SELECT list of field here From TblItems I INNER JOIN tblOrdersItems OI ON I.itemID=OI.itemID
WHERE OI.orderID=MyIDHere
|||You can use the following query,
Code Snippet
Select * from tblOrders A
Join tblOrdersItems B on A.orderId = b.orderId
Join tblItems C on C.itemId = B.ItemId
Where
A.orderId = @.YourOrderId
sqlRelationships on MSDE
I am an Access user.
In Access when I create a relationship between 2 tables is use
Enforce Referential Integrity
Cascade Update Related Fields
Cascade Delete Related Records
If I change data in Primary Table is Update automaticaly in Foreign table.
I use a MSDE database, and I create 2 tabele
Table 1 - with a primary key (AUT_ID)
Table 2 (Foreign table) with 2 fields
Field :AUT_ID
Field: Field1
I use Access interfaces (adp Database) and I create a Diagram
Primary key table: Table_1; Field: AUT_ID
Foreign table: Table_2; Field: AUT_ID
PROBLEM: If I change data in Table_1 (field AUT_ID) data is not change in Table_2 and error occurs.[i]
PROBLEM: If I change data in Table_1 (field AUT_ID) data is not change in Table_2 and error occurs.
I see nothing that requests any sort of cascading-update.
Furthermore, Table_2 is clearly the master-table of the relationship and Table_1 the subordinate. Thus a change to Table_1 to introduce a key not in Table_2 would be disallowed, as you see.|||Is a version problem if I understud corectly
SQL vers.7 did not suport ON UPDATE NO ACTION / CASCADE
SQL vers.8 (suport ON UPDATE NO ACTION / CASCADE)
I installed vers.8 and everything is OK.
Thanks.
In Access when I create a relationship between 2 tables is use
Enforce Referential Integrity
Cascade Update Related Fields
Cascade Delete Related Records
If I change data in Primary Table is Update automaticaly in Foreign table.
I use a MSDE database, and I create 2 tabele
Table 1 - with a primary key (AUT_ID)
Table 2 (Foreign table) with 2 fields
Field :AUT_ID
Field: Field1
I use Access interfaces (adp Database) and I create a Diagram
Primary key table: Table_1; Field: AUT_ID
Foreign table: Table_2; Field: AUT_ID
PROBLEM: If I change data in Table_1 (field AUT_ID) data is not change in Table_2 and error occurs.[i]
PROBLEM: If I change data in Table_1 (field AUT_ID) data is not change in Table_2 and error occurs.
I see nothing that requests any sort of cascading-update.
Furthermore, Table_2 is clearly the master-table of the relationship and Table_1 the subordinate. Thus a change to Table_1 to introduce a key not in Table_2 would be disallowed, as you see.|||Is a version problem if I understud corectly
SQL vers.7 did not suport ON UPDATE NO ACTION / CASCADE
SQL vers.8 (suport ON UPDATE NO ACTION / CASCADE)
I installed vers.8 and everything is OK.
Thanks.
Wednesday, March 28, 2012
Relationships in EM
Hi, What's "Enforce relationship for replication" for? pls advice.On Mon, 31 Jan 2005 12:44:20 -0500, "js" <js@.someone@.hotmail.com> wrote:
>Hi, What's "Enforce relationship for replication" for? pls advice.
Hi js,
Check out Books Online: Index - disabling foreign keys / Disabling a
Foreign Key Constraint for Replication.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
>Hi, What's "Enforce relationship for replication" for? pls advice.
Hi js,
Check out Books Online: Index - disabling foreign keys / Disabling a
Foreign Key Constraint for Replication.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Relationships between Report and SubReport
Hi,
Is there any way to establish a relationship between multiple datasets
generated at the report level?
For example is it possible to run two queries and reference the columns to
do in memory joins? The client we are working for has a poor data structure
and unfortunately our hands are tied so we need to work with multiple
queries across datasets.
Thanks for any info!
RonThe way you do this is with subreports. There is no way to join in memory
datasets.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:u%23Fc%23e04HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is there any way to establish a relationship between multiple datasets
> generated at the report level?
> For example is it possible to run two queries and reference the columns to
> do in memory joins? The client we are working for has a poor data
> structure and unfortunately our hands are tied so we need to work with
> multiple queries across datasets.
> Thanks for any info!
> Ron
>
Is there any way to establish a relationship between multiple datasets
generated at the report level?
For example is it possible to run two queries and reference the columns to
do in memory joins? The client we are working for has a poor data structure
and unfortunately our hands are tied so we need to work with multiple
queries across datasets.
Thanks for any info!
RonThe way you do this is with subreports. There is no way to join in memory
datasets.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:u%23Fc%23e04HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is there any way to establish a relationship between multiple datasets
> generated at the report level?
> For example is it possible to run two queries and reference the columns to
> do in memory joins? The client we are working for has a poor data
> structure and unfortunately our hands are tied so we need to work with
> multiple queries across datasets.
> Thanks for any info!
> Ron
>
Relationship to UserId
Hi there everyone, this is my first post so go easy on me :)
Basically I am trying to get my database to copy the value in the UserId (unique identifier field) from the aspnet_Users table to a foreign key UserId in a table called userclassset. I have made this field the same datatype and created a relationship between the two. Unfortunately, when I add a user using the ASP.Net configuration tool it does not automatically copy this value into my own custom table. I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great!
Thanks :)
ascension:
?I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great!
This is because when you?use?.NET2.0?built-in?function?to create user,?the?aspnet_Users_CreateUser?stored?procedure?is?called.?This?stored?procedure?will?not?only?insert?a?row?in?aspnet_users?table,?but?also?do?some?operations?on?aspnet_Memebership?table,?you?can?use?sp_helptext?to?view?the?definition:
sp_helptext aspnet_Users_CreateUser
So if you want to insert some row into your own custom table, you can either: alter the aspnet_Users_CreateUser to add your own statements (preferred); orcreate a?insert trigger?on?the?aspnet_users?table|||Brilliant, worked perfectly. Thanks :)
Relationship to table in another DB possible?
Is it possible to create a relationship to a table in another database,
either on the same server or a linked server?
- Dave
Hi
Foreign Keys are not possible across databases, you can create a check
constraint that calls a function (which may prove slow!) or enforce the
constraint in a trigger.
John
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>
|||Creating a foreign key across databases is not possible; however, you can
enforce cross-database relationships using triggers.
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>
either on the same server or a linked server?
- Dave
Hi
Foreign Keys are not possible across databases, you can create a check
constraint that calls a function (which may prove slow!) or enforce the
constraint in a trigger.
John
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>
|||Creating a foreign key across databases is not possible; however, you can
enforce cross-database relationships using triggers.
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>
Relationship to table in another DB possible?
Is it possible to create a relationship to a table in another database,
either on the same server or a linked server?
- DaveHi
Foreign Keys are not possible across databases, you can create a check
constraint that calls a function (which may prove slow!) or enforce the
constraint in a trigger.
John
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>|||Creating a foreign key across databases is not possible; however, you can
enforce cross-database relationships using triggers.
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>
either on the same server or a linked server?
- DaveHi
Foreign Keys are not possible across databases, you can create a check
constraint that calls a function (which may prove slow!) or enforce the
constraint in a trigger.
John
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>|||Creating a foreign key across databases is not possible; however, you can
enforce cross-database relationships using triggers.
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>
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
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
relationship question
I am having a debate with someone here at work on how a database should be laid out. We have a table of ads for boats. An ad can have 0 to 8 pictures. Do you create 8 columns in the ad recordset for the possible 8 photos for that ad or do you create a separate table that has the ad_ID and a new row for whatever pictures are associated with it. Pictures can be added and deleted at anytime. Currently we are naming the photos with the ad_ID then a number like 46_1.jpg, 46_2.jpg. We are only storing the file name, not the data in the database. We are using SQL not Access.Well, if you create a table with "ad_ID then a number like 46_1.jpg, 46_2.jpg" ... then your select statement is very simple. "Select * from ad_table where ad_ID = ??" and your insert statement is simple. However, if you create a table with ad_id, ad1, ad2, ad3, ad4, ad5, ad6, ad7, ad8 .... then you are limited to 8 pictures for that ad ... if you some day need ad9 you will have to alter the table. Either way works but one is more scalable.|||Originally posted by lisa1958
I am having a debate with someone here at work on how a database should be laid out. We have a table of ads for boats. An ad can have 0 to 8 pictures. Do you create 8 columns in the ad recordset for the possible 8 photos for that ad or do you create a separate table that has the ad_ID and a new row for whatever pictures are associated with it. Pictures can be added and deleted at anytime. Currently we are naming the photos with the ad_ID then a number like 46_1.jpg, 46_2.jpg. We are only storing the file name, not the data in the database. We are using SQL not Access.
Well, using the Normal Forms (i thought the third one), the answer should be : store the possible pictures of an ad in a seperate table. Since you don't know how many pictures there'll be for an ad, you don't want to create columns for. Just think of the possibility that in the future more than 8 pictures must be stored for an ad, and u only have 8 columns for it in the ad table. In that case you should change the datamodel and all kinds of interfaces. Brrrrrrrrr
So, create a table for the ad, a table for the ad-pictures and a foreign key between them
Hope this helps
I am having a debate with someone here at work on how a database should be laid out. We have a table of ads for boats. An ad can have 0 to 8 pictures. Do you create 8 columns in the ad recordset for the possible 8 photos for that ad or do you create a separate table that has the ad_ID and a new row for whatever pictures are associated with it. Pictures can be added and deleted at anytime. Currently we are naming the photos with the ad_ID then a number like 46_1.jpg, 46_2.jpg. We are only storing the file name, not the data in the database. We are using SQL not Access.
Well, using the Normal Forms (i thought the third one), the answer should be : store the possible pictures of an ad in a seperate table. Since you don't know how many pictures there'll be for an ad, you don't want to create columns for. Just think of the possibility that in the future more than 8 pictures must be stored for an ad, and u only have 8 columns for it in the ad table. In that case you should change the datamodel and all kinds of interfaces. Brrrrrrrrr
So, create a table for the ad, a table for the ad-pictures and a foreign key between them
Hope this helps
Relationship Problem
Hi
Fairly new to SQL so apologies if this is really dumb.
Can anyone see what is wrong with the relationship here
(http://www.step-online.org.uk/Diagram.png)
I two many-to-many relationships which I have tried to make a
many-to-many-to-many relationship out of!
I have Individuals and Courses.
Courses can be held at Many Locations by Many Tutors - many-to-many
relationship 1 - this works fine.
Many Individuals can attend Many Courses - so I have created another join
table and tried joining it to the courses table (which is part of the
previous many-to-many join).
It works, but I get each result multiplied x 4. So if I enter that one
individual attended 1 course and run it, it gives me the result 4 times!
Making it look like he attended the same course 4 times!
Have I done something wrong here?Forgot to say - there is a 3rd many-to-many relationship involved!
An Individual can be linked to many Businesses also!
"Keith" <@..> wrote in message news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Fairly new to SQL so apologies if this is really dumb.
> Can anyone see what is wrong with the relationship here
> (http://www.step-online.org.uk/Diagram.png)
> I two many-to-many relationships which I have tried to make a
> many-to-many-to-many relationship out of!
> I have Individuals and Courses.
> Courses can be held at Many Locations by Many Tutors - many-to-many
> relationship 1 - this works fine.
> Many Individuals can attend Many Courses - so I have created another join
> table and tried joining it to the courses table (which is part of the
> previous many-to-many join).
> It works, but I get each result multiplied x 4. So if I enter that one
> individual attended 1 course and run it, it gives me the result 4 times!
> Making it look like he attended the same course 4 times!
> Have I done something wrong here?
>|||If a single instance of a course given at a particular time can have
multiple tutors ( say 4), I can see where you might get 4 rows.. It would be
better for you to post your query also..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Fairly new to SQL so apologies if this is really dumb.
> Can anyone see what is wrong with the relationship here
> (http://www.step-online.org.uk/Diagram.png)
> I two many-to-many relationships which I have tried to make a
> many-to-many-to-many relationship out of!
> I have Individuals and Courses.
> Courses can be held at Many Locations by Many Tutors - many-to-many
> relationship 1 - this works fine.
> Many Individuals can attend Many Courses - so I have created another join
> table and tried joining it to the courses table (which is part of the
> previous many-to-many join).
> It works, but I get each result multiplied x 4. So if I enter that one
> individual attended 1 course and run it, it gives me the result 4 times!
> Making it look like he attended the same course 4 times!
> Have I done something wrong here?
>|||My tables are just full of dummy info at the moment.
2 records in every table with the exception of individual which has 3
records.
There are 3 individual-business joins, 6 course joins, and 1
individual-course join (the one that gives me 4 copies).
Simple SQL statement below (this will not be the final statement - just
testing that it worked).
Thanks
SELECT dbo.SYS_Individual.IND_First_Name,
dbo.SYS_Individual.IND_Surname, dbo.SYS_Courses.COURSE_Title
FROM dbo.SYS_Courses INNER JOIN
dbo.SYS_Xref_Join_Courses ON dbo.SYS_Courses.COURSE_ID
= dbo.SYS_Xref_Join_Courses.COURSE_ID INNER JOIN
dbo.SYS_Courses_TimeTable ON
dbo.SYS_Xref_Join_Courses.COURSE_TT_ID =
dbo.SYS_Courses_TimeTable.COURSE_TT_ID INNER JOIN
dbo.SYS_Courses_Tutors ON
dbo.SYS_Xref_Join_Courses.COURSE_TUT_ID =
dbo.SYS_Courses_Tutors.COURSE_TUT_ID INNER JOIN
dbo.SYS_Courses_Venues ON
dbo.SYS_Xref_Join_Courses.COURSE_VEN_ID =
dbo.SYS_Courses_Venues.COURSE_VEN_ID INNER JOIN
dbo.SYS_Xref_Join_Ind_Courses ON
dbo.SYS_Courses.COURSE_ID = dbo.SYS_Xref_Join_Ind_Courses.COURSE_ID INNER
JOIN
dbo.SYS_Individual ON
dbo.SYS_Xref_Join_Ind_Courses.IND_ID = dbo.SYS_Individual.IND_ID
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:O6wMiNxFEHA.576@.TK2MSFTNGP11.phx.gbl...
> If a single instance of a course given at a particular time can have
> multiple tutors ( say 4), I can see where you might get 4 rows.. It would
be
> better for you to post your query also..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Keith" <@..> wrote in message
news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
join
>
Fairly new to SQL so apologies if this is really dumb.
Can anyone see what is wrong with the relationship here
(http://www.step-online.org.uk/Diagram.png)
I two many-to-many relationships which I have tried to make a
many-to-many-to-many relationship out of!
I have Individuals and Courses.
Courses can be held at Many Locations by Many Tutors - many-to-many
relationship 1 - this works fine.
Many Individuals can attend Many Courses - so I have created another join
table and tried joining it to the courses table (which is part of the
previous many-to-many join).
It works, but I get each result multiplied x 4. So if I enter that one
individual attended 1 course and run it, it gives me the result 4 times!
Making it look like he attended the same course 4 times!
Have I done something wrong here?Forgot to say - there is a 3rd many-to-many relationship involved!
An Individual can be linked to many Businesses also!
"Keith" <@..> wrote in message news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Fairly new to SQL so apologies if this is really dumb.
> Can anyone see what is wrong with the relationship here
> (http://www.step-online.org.uk/Diagram.png)
> I two many-to-many relationships which I have tried to make a
> many-to-many-to-many relationship out of!
> I have Individuals and Courses.
> Courses can be held at Many Locations by Many Tutors - many-to-many
> relationship 1 - this works fine.
> Many Individuals can attend Many Courses - so I have created another join
> table and tried joining it to the courses table (which is part of the
> previous many-to-many join).
> It works, but I get each result multiplied x 4. So if I enter that one
> individual attended 1 course and run it, it gives me the result 4 times!
> Making it look like he attended the same course 4 times!
> Have I done something wrong here?
>|||If a single instance of a course given at a particular time can have
multiple tutors ( say 4), I can see where you might get 4 rows.. It would be
better for you to post your query also..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Fairly new to SQL so apologies if this is really dumb.
> Can anyone see what is wrong with the relationship here
> (http://www.step-online.org.uk/Diagram.png)
> I two many-to-many relationships which I have tried to make a
> many-to-many-to-many relationship out of!
> I have Individuals and Courses.
> Courses can be held at Many Locations by Many Tutors - many-to-many
> relationship 1 - this works fine.
> Many Individuals can attend Many Courses - so I have created another join
> table and tried joining it to the courses table (which is part of the
> previous many-to-many join).
> It works, but I get each result multiplied x 4. So if I enter that one
> individual attended 1 course and run it, it gives me the result 4 times!
> Making it look like he attended the same course 4 times!
> Have I done something wrong here?
>|||My tables are just full of dummy info at the moment.
2 records in every table with the exception of individual which has 3
records.
There are 3 individual-business joins, 6 course joins, and 1
individual-course join (the one that gives me 4 copies).
Simple SQL statement below (this will not be the final statement - just
testing that it worked).
Thanks
SELECT dbo.SYS_Individual.IND_First_Name,
dbo.SYS_Individual.IND_Surname, dbo.SYS_Courses.COURSE_Title
FROM dbo.SYS_Courses INNER JOIN
dbo.SYS_Xref_Join_Courses ON dbo.SYS_Courses.COURSE_ID
= dbo.SYS_Xref_Join_Courses.COURSE_ID INNER JOIN
dbo.SYS_Courses_TimeTable ON
dbo.SYS_Xref_Join_Courses.COURSE_TT_ID =
dbo.SYS_Courses_TimeTable.COURSE_TT_ID INNER JOIN
dbo.SYS_Courses_Tutors ON
dbo.SYS_Xref_Join_Courses.COURSE_TUT_ID =
dbo.SYS_Courses_Tutors.COURSE_TUT_ID INNER JOIN
dbo.SYS_Courses_Venues ON
dbo.SYS_Xref_Join_Courses.COURSE_VEN_ID =
dbo.SYS_Courses_Venues.COURSE_VEN_ID INNER JOIN
dbo.SYS_Xref_Join_Ind_Courses ON
dbo.SYS_Courses.COURSE_ID = dbo.SYS_Xref_Join_Ind_Courses.COURSE_ID INNER
JOIN
dbo.SYS_Individual ON
dbo.SYS_Xref_Join_Ind_Courses.IND_ID = dbo.SYS_Individual.IND_ID
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:O6wMiNxFEHA.576@.TK2MSFTNGP11.phx.gbl...
> If a single instance of a course given at a particular time can have
> multiple tutors ( say 4), I can see where you might get 4 rows.. It would
be
> better for you to post your query also..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Keith" <@..> wrote in message
news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
join
>
Relationship Problem
Hello all,
OK - my problem is that my relationship(s) just doesn't seem to work.
I am currently using Microsoft SQL Server 2005, and working through the management studio. For my site coding, I'm using Coldfusion.
So you can expect that I don't know an awful lot of actual SQL code, and instead am relying on the interface.
I have already achieved making a fully functional registeration and login page for my site, so there's no hiccups there :)
But when I insert a new user to my "users" table, the userid doesn't seem to appear my other table; "characters". I should probably note that by characters, I mean personalities/people - not actual text characters or whatnot.
Here's the low-down on my current DB layout:
My first table is "users" and has the columns "userid", "username", "password", and "emailaddress". The userid column is a PK with identity specification. It is also an int datatype, while the other columns are varchar(20)s. Also, the userid doesn't allow nulls, obviously.
My second table is "characters" and has several columns, mostly of the same datatypes as the "users" table - so it's not relevant. The two important columns are "characterid" and "userid" - both are int datatypes and do not allow nulls. The characterid column serves as the PK and has identity specification.
Now comes the problem...; using a database diagram, I have created a relationship between "userid" from "users", and "userid" from "characters". The "userid" in "characters, in theory, is the FK.
Right. So I register myself on the site using a simple insert sql query (actually, I use <cfinsert>) - the insert goes directly into "users" with a username, password, and email address. A unique number is created in userid, as you'd expect... but nothing happens in "characters" at all!
How do I get the userid to pass on from "users" into "characters"?
Well crap, this post is extremely long now. I only hope it's understandable, and that someone can help me :(
Thanks a lot in advance!
AidenFK is a logical object and not physical that can insert record for you.
You have to create a second insert statement into a characters table but getting newly created userid first.
Good Luck.|||Hmm, thanks for the reply..
I think I tried doing that already.
I first used the insert sql statement and inserted a username, password, and email address into the "users" table - I can confirm that this was successful. That then created the userid automatically.
Then, another insert sql statement was ran straight after, which also inserted information into fullname, gender, and class in the "characters" table - this also was successful. The characterid was successfully added automatically, but nothing happened in the userid column (in "characters").
Am I going about this right?
More on the subject... I was under the impression that relationships were used to automatically make specific entries from a column in one table, copy over to a similar column in another table - therefore making queries from different tables always follow the same userid (for example). This would then make sure the site user always views only his or her row information, no matter the table queried.|||You have a wrong impression.
In second insert statement instead of userid use following statement
Insert into characters( "userid", ) values( (select userid from users where username = "username" and password = "password" and emailaddress = "emailaddress"), other columns here)
So my point is you can use select statement instead of userid.
FK works like this:
If you would try entering some userid into characters table that does not exist in users table it will give you an error.
I would recommend creating unique index on users table over (username, password, emailaddress) columns so you wouldnt have duplicates which might cause problem at insert time. If you want to have multiple records for the same user then you can change your insert statement to this:
Insert into characters( "userid", ) values( (select max(userid) from users where username = "username" and password = "password" and emailaddress = "emailaddress"), other columns here)
Good Luck.|||I understand now :) I guess I was expecting something more automated with the use of relationships.
But I still don't entirely understand the point in having a relationship.
Even without the relationship, I can still add the userid from "users" into "characters" in the way that you described. So long as I have an identity specification on my userid in "users", there will never be an error related to a duplicate userid.
EDIT: I could also use code to check that the userid is currently available and exists from "users", before trying to create it within "characters", thus eliminating the need for a relationship? I think?...
That aside, your solution has been very helpful to me :) I can finally continue with the rest of my site!
Thanks
Aiden|||select userid from users where username = "username" and password = "password" and emailaddress = "emailaddress"
Statement above can return 2 different userids it will be generated for you but it will be escentially the same user. If you have identity column you can use @.@.IDENTITY global variable in the next statement instead of select statement above.
Foreign key just to make sure primary key exists in a parent table.
Godd Luck.
Relationship problem
Hi All... Two of my tables are:
Users - primary key is UserId, an int with identity turned on.
Messages - has a column named UserId that references the same in Users.
I'm using Visual Studio 2005 against a SQL 2005 database.
Using both the diagram tool and table data, I'm trying to set up the relationship implied above and am getting the following error:
Users table saved successfully.
Messages table
- unable to create relationship 'FK_Messages_UserId'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 'FK_Messages_UserId'. The conflict occurred in database 'XXXX', table 'dbo.Users', column 'UserId'.
I've done several other similar relationships without incident. But this one (and one or two others) refuse to work. I'm a bit of newbie with these rascals, so that doesnt help much... Any ideas what this things trying to tell me? Thanks! -- Curt
You might already have entries in your child table which have no parent entry in the parent table.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Hi Jens... Thanks for the reply. You threw me a little at first on your use of "parent" and "child", but yeah you nailed it. To try and tie those terms to the tables in my original post, I had some records in Messages (child) that referenced a primary key that did not exist in Users (parent). Geez, these FKs really help us keep a clean house, dont they... Thanks again!! Curt
Relationship Policies - Conditions
Hi!
Is it possible to include several conditions into a single policy?
Thanks
Klaus Aschenbrenner
http://www.csharp.at
http://www.csharp.at/blog
Hi Klaus,
Currently you cannot associate multiple conditions to a single policy. Can you provide an example of when you would want to associate multiple conditions to a single policy.
Cheers,
Dan
sqlRelationship map using CTEs
For instance, if I have the following values in a table
Id ParentId
1 null
2 1
3 1
4 2
5 4
I understand how to traverse this relationship tree using CTE but how would
I build a map where I say Id 1 is related to Id 2, 3, 4, 5... Like so,
Return results
Id RelatedId
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 4
2 5
3 1
3 3
4 1
4 2
4 4
4 5
5 1
5 2
5 4
5 5
Basically, an Id is related to every id that descends from it (directly or
indirectly) and an id is related to every parent link upwards from it.
Is this possible with CTEs?"Arif" <Arif@.discussions.microsoft.com> wrote in message
news:2B66D0F9-7E43-41C1-9B93-6DF147E5EB83@.microsoft.com...
> For instance, if I have the following values in a table
> Id ParentId
> 1 null
> 2 1
> 3 1
> 4 2
> 5 4
>
> I understand how to traverse this relationship tree using CTE but how
> would
> I build a map where I say Id 1 is related to Id 2, 3, 4, 5... Like so,
>
> Return results
> Id RelatedId
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
> 2 1
> 2 2
> 2 4
> 2 5
> 3 1
> 3 3
> 4 1
> 4 2
> 4 4
> 4 5
> 5 1
> 5 2
> 5 4
> 5 5
>
> Basically, an Id is related to every id that descends from it (directly or
> indirectly) and an id is related to every parent link upwards from it.
> Is this possible with CTEs?
Yes. Here's an example of how to enumerate a transative relation using a
CTE:
Create Table T
(
ID int primary key,
ParentID int references T
)
insert into t(id,parentid)
select 1,null
union all select 2,1
union all select 3,1
union all select 4,2
union all select 5,4
go
with Related(ID, RelatedID, Distance)
as
(
--anchor with reflexive member
select ID, ID, 0 from T
--union in transitively related members
union all
select r.ID, t.ID, r.Distance + 1
from T t join Related r
on t.ParentID = r.RelatedID
)
select * from Related
order by id, RelatedID, Distance
/* results
ID RelatedID Distance
-- -- --
1 1 0
1 2 1
1 3 1
1 4 2
1 5 3
2 2 0
2 4 1
2 5 2
3 3 0
4 4 0
4 5 1
5 5 0
*/
David
Id ParentId
1 null
2 1
3 1
4 2
5 4
I understand how to traverse this relationship tree using CTE but how would
I build a map where I say Id 1 is related to Id 2, 3, 4, 5... Like so,
Return results
Id RelatedId
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 4
2 5
3 1
3 3
4 1
4 2
4 4
4 5
5 1
5 2
5 4
5 5
Basically, an Id is related to every id that descends from it (directly or
indirectly) and an id is related to every parent link upwards from it.
Is this possible with CTEs?"Arif" <Arif@.discussions.microsoft.com> wrote in message
news:2B66D0F9-7E43-41C1-9B93-6DF147E5EB83@.microsoft.com...
> For instance, if I have the following values in a table
> Id ParentId
> 1 null
> 2 1
> 3 1
> 4 2
> 5 4
>
> I understand how to traverse this relationship tree using CTE but how
> would
> I build a map where I say Id 1 is related to Id 2, 3, 4, 5... Like so,
>
> Return results
> Id RelatedId
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
> 2 1
> 2 2
> 2 4
> 2 5
> 3 1
> 3 3
> 4 1
> 4 2
> 4 4
> 4 5
> 5 1
> 5 2
> 5 4
> 5 5
>
> Basically, an Id is related to every id that descends from it (directly or
> indirectly) and an id is related to every parent link upwards from it.
> Is this possible with CTEs?
Yes. Here's an example of how to enumerate a transative relation using a
CTE:
Create Table T
(
ID int primary key,
ParentID int references T
)
insert into t(id,parentid)
select 1,null
union all select 2,1
union all select 3,1
union all select 4,2
union all select 5,4
go
with Related(ID, RelatedID, Distance)
as
(
--anchor with reflexive member
select ID, ID, 0 from T
--union in transitively related members
union all
select r.ID, t.ID, r.Distance + 1
from T t join Related r
on t.ParentID = r.RelatedID
)
select * from Related
order by id, RelatedID, Distance
/* results
ID RelatedID Distance
-- -- --
1 1 0
1 2 1
1 3 1
1 4 2
1 5 3
2 2 0
2 4 1
2 5 2
3 3 0
4 4 0
4 5 1
5 5 0
*/
David
relationship inside the same table
i ve got a database that has a table...that table has a relationship between its primary key,and another field,
actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...
the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)
i hope i ll have an answer soon,and thanks in advanced
PS: i am using MSSQL 2000 evaluation
You will need to write a trigger to meet this need. Unfortunately SQL Server does not handle the situation you describe.
|||Consider using the nested-set approach. SELECT and DELETE queries are a breeze, allowing everything in one simple statement.|||very strange...access did!!!
are u sure?|||i had to write a trigger...this is one
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @.@.ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted);
END
then to enable recursive triggers in my database options...otherwise it will do the trigger for one level ;)
actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...
the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)
i hope i ll have an answer soon,and thanks in advanced
PS: i am using MSSQL 2000 evaluation
You will need to write a trigger to meet this need. Unfortunately SQL Server does not handle the situation you describe.
|||Consider using the nested-set approach. SELECT and DELETE queries are a breeze, allowing everything in one simple statement.|||very strange...access did!!!
are u sure?|||i had to write a trigger...this is one
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @.@.ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted);
END
then to enable recursive triggers in my database options...otherwise it will do the trigger for one level ;)
Relationship in Access, what about SQL Server 2000?
In Microsoft Access, there is a feature to create relationships between tables. When creating these relations, you have the option to:
1. Establish the relation type. For example, 1 to many (primary key to foreign key)
2. Enforce referential integrity
3. Allow cascading updates
4. Allow cascading deletions
5. View these relations in a nice diagram
Does SQL Server 2000 contain this functionality? If so, where is this found in the Enterprise Manager? If not, what is the alternative to this very useful feature found in Access?
Thanks You For Any Help!
You can also use the design view or sql command to do this.
If you want to use the design view to add relationship, do the followings
Open EM|||HIdefyant_2004
Sql server gives to us many features and there are many ways to create relationship between two tables. Here is the common way to create
1. Open EM
2. Right_click on Diagrams
3. Select New Database Diagram...
4. Next
5. Select the tables you want to create relationship
6. Next step...like Access
Cheers!
Relationship diagrams in SQL mobile 2k5
I looked in the manual as to how I should go about putting a realtionship diagram for my database. Unfortunatly i do not see the Diagram node in the object browser they are reffering to. Does anyone know how I can create a relationship diagram for my mobile database?
You can choose Add Data Connection in Visual Studio 2005, connect to your SQL Mobile database, and then use the Query Designer, choosing all of the tables you wish to see in your diagram. The diagram is presented right there in Visual Studio 2005.
Darren
Relationship Diagrams
Hello,
I was wondering if there is a tool available that maps the
relationships between tables in a SQL Server database.
I need this to help document a user guide to a DB I've designed for
them and they are familair with the MS Access diagrams and can't get
on with the diagrams Enterprise Manager produces.
However I want the tool to "attach" the relations to the Primary Key
and Foreign Keys regardless of where I drag the tables around, an
awful lot like MS Access relationship diagrams do. I've trawled
through many "trails" offered on the internet and thought I'd be
better off "asking the audience" for any recommendations.
Thanks for any help
Giles
If you want to document your database, you might check out SqlSpec, a
shareware app I wrote: http://www.elsasoft.org
Giles wrote:
>Hello,
>I was wondering if there is a tool available that maps the
>relationships between tables in a SQL Server database.
>I need this to help document a user guide to a DB I've designed for
>them and they are familair with the MS Access diagrams and can't get
>on with the diagrams Enterprise Manager produces.
>However I want the tool to "attach" the relations to the Primary Key
>and Foreign Keys regardless of where I drag the tables around, an
>awful lot like MS Access relationship diagrams do. I've trawled
>through many "trails" offered on the internet and thought I'd be
>better off "asking the audience" for any recommendations.
>Thanks for any help
>Giles
|||On 17 May, 21:29, "jezemine" <u34287@.uwe> wrote:
> If you want to document your database, you might check out SqlSpec, a
>
> shareware app I wrote:http://www.elsasoft.org
> Giles wrote:
>
>
> - Show quoted text -
Thanks for the tip, pretty looking app, but unfortunatley it still
doesn't map the relationships as I require.
Ta
Giles
sql
I was wondering if there is a tool available that maps the
relationships between tables in a SQL Server database.
I need this to help document a user guide to a DB I've designed for
them and they are familair with the MS Access diagrams and can't get
on with the diagrams Enterprise Manager produces.
However I want the tool to "attach" the relations to the Primary Key
and Foreign Keys regardless of where I drag the tables around, an
awful lot like MS Access relationship diagrams do. I've trawled
through many "trails" offered on the internet and thought I'd be
better off "asking the audience" for any recommendations.
Thanks for any help
Giles
If you want to document your database, you might check out SqlSpec, a
shareware app I wrote: http://www.elsasoft.org
Giles wrote:
>Hello,
>I was wondering if there is a tool available that maps the
>relationships between tables in a SQL Server database.
>I need this to help document a user guide to a DB I've designed for
>them and they are familair with the MS Access diagrams and can't get
>on with the diagrams Enterprise Manager produces.
>However I want the tool to "attach" the relations to the Primary Key
>and Foreign Keys regardless of where I drag the tables around, an
>awful lot like MS Access relationship diagrams do. I've trawled
>through many "trails" offered on the internet and thought I'd be
>better off "asking the audience" for any recommendations.
>Thanks for any help
>Giles
|||On 17 May, 21:29, "jezemine" <u34287@.uwe> wrote:
> If you want to document your database, you might check out SqlSpec, a
>
> shareware app I wrote:http://www.elsasoft.org
> Giles wrote:
>
>
> - Show quoted text -
Thanks for the tip, pretty looking app, but unfortunatley it still
doesn't map the relationships as I require.
Ta
Giles
sql
Relationship diagram
Hi there,
I just set up my AppData and i'm trying to connect the Membership to a tbl_Profile that i created.
I notice there's an application_id and a user_id which are uniqueidentifiers. I've been trying create a relationship to tbl_Profile's user_id column but it won't let me saying it's not compatible. Am i suppose to set tbl_Profile's user_id column to int or uniqueidentifier? I've tried both and it won't let me...
THanks!
Hello lilboi,
Are there records in the table of which you try to change the column to int or uniqueidentifier?
When the original column type is char(n)/string, you can't change it to int or uniqueidentifier, because the conversion fails.
What relationship are you trying to create? Between what tables and what fields?
Jeroen Molenaar.
|||hi jeroenm!
There are no records yet. So I was able to have it as int, but it wouldn't connect. THen i changed it to uniqueidentifier but it still wouldn't connect.
WHen i mean by connecting, i meant, when i try to draw a relationship between the column user_id for both the tables.
Thanks!
Let me try to explain:
aspnet_Membership tbl_Profile
---------- -----
user_id 1----to----1 user_id
I try to draw a 1 to 1 relationship by going through Relationship Diagram and then dragging user_id(Profile) to user_id(Membership)
Subscribe to:
Posts (Atom)