I want to create primary key after inserting foreign key.

Question

I want to create primary key after inserting foreign key.

aspardeshi

SSC Eights!

Points: 886
More actions
October 14, 2010 at 10:42 pm

#227969

Hello Professionals,
I have a typical scenario. I have a table for students. When I enter username required for parents in student master the parent master table needs to be filled with link to that student. So here parent master is primary table and student master is foreign table (I hope so, any workaround welcome) in student master the username is a required field.
Now here my first problem is that I cannot create a before insert trigger in student master. Secondly in the after insert trigger in student detail table, if I try to fill the parent master table, the error occur for the constraint which I have given from parents to students. Maybe that error is because there is no master record exist for the student detail record. Here I am entering detail student record first.
Any great ideas is highly appreciated. I have been thinking for the whole night, but could not find solution, it is coming on my nerves now. If this was in oracle, I would have done in seconds ! There must be some workaround here which I doesn't know in sql server 2005.
I was very good at sql but variety spoiled me ! :w00t:

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

aspardeshi (10/14/2010)
Hello Professionals,
I have a typical scenario. I have a table for students. When I enter username required for parents in student master the parent master table needs to be filled with link to that student. So here parent master is primary table and student master is foreign table (I hope so, any workaround welcome) in student master the username is a required field.
Now here my first problem is that I cannot create a before insert trigger in student master. Secondly in the after insert trigger in student detail table, if I try to fill the parent master table, the error occur for the constraint which I have given from parents to students. Maybe that error is because there is no master record exist for the student detail record. Here I am entering detail student record first.
Any great ideas is highly appreciated. I have been thinking for the whole night, but could not find solution, it is coming on my nerves now. If this was in oracle, I would have done in seconds ! There must be some workaround here which I doesn't know in sql server 2005.

I guess I need to understand why you're using triggers instead of normal DRI.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aspardeshi SSC Eights! Points: 886 More actions · Answer 2

Hi Jeff,

Please let me know what is DRI. I found a workaround though which can be helpful, can you disable the constraint of foreign key related to that master table , while inserting and then after inserting , enable the constraint command in trigger for insert ? if there is any workaround please let me know. I am stucked.

If you have any readymade code please post it here. I am a complete noob in disabling and enabling constraint from a insert trigger 🙂

thank you in advance.

I was very good at sql but variety spoiled me ! :w00t:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

aspardeshi (10/15/2010)
Please let me know what is DRI.

Declarative Referential Integrity

I found a workaround though which can be helpful, can you disable the constraint of foreign key related to that master table , while inserting and then after inserting , enable the constraint command in trigger for insert ?

That is seriously, seriously bad practice. If I ever found anything like that in code that I was reviewing it would be back with the developer so fast they'd have whiplash.

I don't understand what you're having a problem with. Insert into the parent table first, the child table second.

Maybe some table structures and sample data would help explain what it is that you're trying to do here

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aspardeshi SSC Eights! Points: 886 More actions · Answer 4

Hi Gila,

Thanks for the update. I will give you sufficient reasoning below the logic, the logic is that every student need not register parent user id, but every parent userid registered should see the details about the students.

It is a bad practice to enter the parent details first while entering the student master (in my knowledge) so in my logic of student master if we put the parent chosen user name then the parent master record will be inserted and it will be connected to that student only. so parents can see his progress in school over the website.

I think I will implement your logic, it can make my day ! The new logic is now every parent should have a userid no matter what :w00t: and then only they can enter students details !

Thanks Gila for your updates.

I was very good at sql but variety spoiled me ! :w00t:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

aspardeshi (10/15/2010)
Thanks for the update. I will give you sufficient reasoning below the logic, the logic is that every student need not register parent user id, but every parent userid registered should see the details about the students.

That's not sufficient detail. Table definitions. Sample data. Detailed explanation.

It is a bad practice to enter the parent details first while entering the student master (in my knowledge) so in my logic of student master if we put the parent chosen user name then the parent master record will be inserted and it will be connected to that student only. so parents can see his progress in school over the website.

Huh?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 6

i think your confusing parent-child relationships;

businesswise, the students and the table associated to them are the key/primary table.

information about the parents may or may not be available, so the table containing parents info is a child/foreign key'd to the core students table.

your problem is you are trying to force the schema to match the family relationship(the mom and dad must exist before the children) , when that's not what the business is tracking/following.

the biz is tracking students and the details that revolve around them.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

aspardeshi (10/15/2010)
Hi Gila,
Thanks for the update. I will give you sufficient reasoning below the logic, the logic is that every student need not register parent user id, but every parent userid registered should see the details about the students.
It is a bad practice to enter the parent details first while entering the student master (in my knowledge) so in my logic of student master if we put the parent chosen user name then the parent master record will be inserted and it will be connected to that student only. so parents can see his progress in school over the website.
I think I will implement your logic, it can make my day ! The new logic is now every parent should have a userid no matter what :w00t: and then only they can enter students details !
Thanks Gila for your updates.

So, you're saying that you can have a Student without a Parent, but not a Parent without a Student? If so, then you've got two approaches. First, the Parent table should be, pardon the syntax, the child table of the Student table, not it's parent (how's that for confusing). Or, because I know this is my situation, you need to create a relationship table so that one Parent can be related to zero or more students (I have two kids, twins, in school at the same time). Something like this.

Student

StudentId (PK)

LotsOfOtherColumns

Parent

ParentId (PK)

LotsOfOtherColumns

ParentStudent

StudentId (FK to Student)

ParentId (FK to Parent)

This way you can insert to Student without inserting to Parent or ParentStudent. You can insert to Parent without inserting to Student or ParentStudent. Once you've got the records established in Student and Parent, you can create the relationship by inserting into ParentStudent.

Anyway, that's how I'd solve the issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

aspardeshi (10/15/2010)
Please let me know what is DRI.

Please do not take this the wrong way but you shouldn't be doing data modeling if you do not know what Referential Integrity is.

Do you have an ER model?

Would you mind sharing it so we can better understand your scenario?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

aspardeshi SSC Eights! Points: 886 More actions · Answer 9

What ER Diagram this noob can share with you professionals ? :hehe: I think Mr. Grant has given a good solution. I will implement it.

Thank you professionals for yours overwhelming response as usual.

I am really pleased of your help. Thank you once again.

I was very good at sql but variety spoiled me ! :w00t: