create relation

Question

create relation

dr_csharp

SSCommitted

Points: 1765
More actions
October 27, 2009 at 11:53 pm

#138986

hi friends
look at this pic for diagram:
[Editor: Image removed]
why i cant have a cascading update,insert for Licence Table ?
now cascading with Employee table is enabled but when i wanna enable cascading with service table it drop error ..

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you provide the error message?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

as u can see in image licence table has 2 relations with Service Table and Employee Table..

i cant set cascading for this 2 relation,SQL just allow for one of them !

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

As requested, can you post the error message please.

Can you also post the code you are executing when you are getting this message.

Without these, everything is guesswork.

dr_csharp SSCommitted Points: 1765 More actions · Answer 4

i dont execute special code,it get error when i want to set update and insert rule to Cascading for second relation..

the error is :

'Services' table saved successfully

'Licences' table

- Unable to create relationship 'FK_Licences_Services'.

Introducing FOREIGN KEY constraint 'FK_Licences_Services' on table 'Licences' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Could not create constraint. See previous errors.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

The error message says it all... have a look at the way you have defined the relationships in your tables, you have a circular reference, so SQL Server is refusing to create a relationship with cascade deletes/updates.

If you try to delete an Office, that will cascade to delete the Employees, which in turn will cascade to delete the Licences. Deleting an Office will also cascade to delete the Services, which in turn will also try to delete the Licences. So, deleting an Office could result in Licences being deleted from 2 different directions.

I think there's something wrong with your data model... you can arrive at Licence from 2 directions. With this model, there's nothing to stop you setting a link that shows an Employee at Office 1 having a Licence for a Service that's provided by Office 2.

BTW... I would not reccommend employing CASCADE deletes/updates. I would much rather get an error message telling me I can't delete an Office because it still has Employees instead of deleting the wrong Office, only to find all its Employees deleted by mistake as well.

dr_csharp SSCommitted Points: 1765 More actions · Answer 6

Ian Scarlett (10/29/2009)

The error message says it all...

I think there's something wrong with your data model... you can arrive at Licence from 2 directions. With this model, there's nothing to stop you setting a link that shows an Employee at Office 1 having a Licence for a Service that's provided by Office 2.

quote]

this is my complete diagram :

[Editor: Image removed]

could u tell me please where is wrong with my model ?

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 7

Without understanding your data and your business, that's very difficult to answer, so I'll go back to my original point.

I presume a licence is granted to a given employee for a given service. The problem I see is that an employee belongs to an office, and a service also seems to belong to an office. Are those 2 offices always the same? If so, one of the links (possibly office to service) is redundant.

dr_csharp SSCommitted Points: 1765 More actions · Answer 8

Ian Scarlett (10/29/2009)
Without understanding your data and your business, that's very difficult to answer, so I'll go back to my original point.
I presume a licence is granted to a given employee for a given service. The problem I see is that an employee belongs to an office, and a service also seems to belong to an office. Are those 2 offices always the same? If so, one of the links (possibly office to service) is redundant.

this is scenario :

services is list of available service provide by Offices

Employees are employees in Offices

Licence is list of permitted services for Employees,for example Employee A can do Service B if there is a record in licence table with that Employee_Id and Service_Id

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 9

dr_csharp (10/29/2009)
services is list of available service provide by Offices
Employees are employees in Offices

But are they always the same office... i.e. can an employee only provide services from the office they are in?

If so, then one of your relationships is redundant.

If not, then you have a circular relationship that is probably legitimate.