Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pseudorand
Starting Member
1 Post |
 Posted - 2005-05-21 : 14:35:43
|
| I need to implement two tables that each have a foreign key to the other. For example, alias.person_id is related to person.id and person.primary_name_id is related to alias.id. This way, I can have a person with multiple names, but also store the pereferred name for that person. The relationships are enforeced with foreign key constraints.The problem comes when I need to do an insert. Since they reference each other, I have to do an `alter table person nocheck constraint all`, insert a person and an alias record with the correct relationships and then turn constraints on again. I do this with an SP called dbo.add_person. The tables are also owned by dbo. The procedure works fine when I execute it from Query Analyzer, but I get a permissions error on the person table when I execute it from my app. The app is running as a user that has access exec on the SP but no access to the table.Shouldn't the SP be able to run 'alter table' since both it and the person table are owned by dbo? Anyone have any ideas about how I can keep my FK constraints but still have my app run with least privs? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-21 : 15:01:33
|
| You need to set the foreign key for alias to allow NULLs. That will solve the issue. You DO NOT want to be altering the table everytime you do this.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-21 : 15:10:17
|
| turning the constraint off then on will take a long time with large tables - and what if the table has something that involidates the constraint - the add will fail and you are running a system without the constraint. Makes the constraint a bit pointless.You could add the person with a null primary name, add the alias then update the primary name.Another option is to have a preferred name flag on the alias entry.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
two-way relationship