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 |
|
skiabox
Posting Yak Master
169 Posts |
 Posted - 2009-07-16 : 15:59:33
|
| I have two related tables.The one is called fees with three columns :fees_id IDENTITY(1,1) NOT NULL,student_id nchar(4) NOT NULL,amount money NOT NULL,CONSTRAINT fees_pk1 (fees_id),CONSTRAINT fees_fk1 FOREIGN KEY (student_id) REFERENCES students.student_id ON UPDATE CASCADEThe other is called students and it has as a foreign key the fees_id.If I try to enter data in any of the two tables I get an error because the related row does not exist yet.Any idea on how to overcome this one?Thank you very much! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-16 : 16:07:35
|
Allow NULL for student_id.Make your insert with student_id = null.update the null value with related student_id...But I would never do that kind of relation as you can see it makes no sense but many problems.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
skiabox
Posting Yak Master
169 Posts |
Posted - 2009-07-16 : 16:18:38
|
| What I want to do is a one-to-one relationship between the students table and their fees which seems logical to me , but I dont want the user of the system to bother with the id of the fees.Any ideas on how to achieve that?Thank you very much again. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-16 : 16:34:37
|
It is really my humble opinion but I cannot resist to post it:Make one table with all data and you will have your one-to-one relation.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
skiabox
Posting Yak Master
169 Posts |
Posted - 2009-07-16 : 16:45:08
|
| That's what I am thinking too.As I read somewhere one-to-one relations show poor database design |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-16 : 16:47:42
|
Nowwearetwo!
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
skiabox
Posting Yak Master
169 Posts |
Posted - 2009-07-16 : 17:26:35
|
| http://www.databaseanswers.org/data_models/dance_school/teachers_students_exams_print_version.htmThis is the same problem here.I have declared the class_id as identity column.It is a many-to-many relationship here so it is another case.Do you believe that it can work this way? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-16 : 17:43:10
|
Yes I believe.You only have to insert data in the right order - but I am sure you know - I don't wanna be a "WISE GUY" 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
How to enter data in these two tables?