How to insert data into a SQL database table with foreign key constraints using C# form?

Question

0.00/5 (No votes)

See more:

Hi! I am a beginner to C# and SQL server.

I have created these tables in SQL server.

1. Subject
-SubjectID (PK)
-SubjectName
-Mark

2. Class
-ClassNo (PK)
-HomeRoomTeacher(FK)
-StudentID(FK)

3. Subject_Class
-SubjectID (PK)
-ClassNo (PK)

4. Teacher
-TeacherID(PK)
-TeacherName
-ClassNo (only the HomeRoomTeacher has the assigned Class)

5. Student
-StudentID(PK)
-StudentName
-ClassNo(FK)

I want to input the following data to the corresponding tables.
Subjects: English, Maths, Physics, Chemistry, Geography, History & Science
Class : 5A,5B,5C,6A,6B,6C,7A,7B,7C,8A,8B,8C,....
Student : John, Mary, Kaito,........
Teacher : Mark, Jane, Rosy,......

Then I tried to input data using C# form. But the exception dialogbox says conflicts with the foreign key constraint.
How can I solve this problem?

What I have tried:

I have been told to try to input data into a table with no foreign keys first or simply don't use foreign keys in real projects. But I don't get it since it is the complete opposite of maintaining data integrity. Moreover, all the tables are linked to each other in the above database. Is there a table that I should input data first?

Posted 9-Jun-18 0:14am

NkaBall

Updated 9-Jun-18 0:36am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-06-09T00:37:00

Think about it: consider an physical invoice.

Customer name
Customer address
Date
Invoice numner
Items:
   12     Widgets           $10.00     $120.00
    5     Thingumies         $1.50       $7.50

Total:                                 $127.50

When you create that, you enter the customer info, allocate an invoice number, set the date - and then start adding items to the invoice - it's the only sensible way to do it!

The electronic version would be three tables:
Customers:

ID
Name
Address

Invoices:

ID
CustomerID
Number
Date

InvoiceItems:

ID
InvoiceID
Count
Description
UnitCost

And the CustomerID and InvoiceID would be Foreign keys.
You can't create an Invoice if you don't have a Customer set up (or you don;t know where to send it!) and you can't create an InvoiceItem unless you have an Invoice to allocate it to!

So you create Customers first, so you can use the CustomerId foreign key in the Invoice, and the Invoice before the InvoiceItem so you can use the InvoiceID foreign key.

Your system is the same: Your Teachers and Subjects will be created before the Classes can be assigned, then the Students can be created and added to the system using the existing info.

Foreign keys are very useful - they help preserve data integrity by making sure you can't delete a Teacher if they are still supposed to have Classes...

But ... why does your Class reference a Student, and your Student reference a Class? Wouldn't a Student have more than one class?