Violation of PRIMARY KEY constraint????

[Twyce]

I am using bulkcopy to import data into SQL server database.The data i'm trying to import contains student numbers and the modules that they are registerd for.A student can be registered for many modules(which means one student number can appear many times on the table).Problem is when i try to import my spradsheet my application throws a "Violation of PRIMARY KEY constraint" error. How do i fix this

[perle1]

Hi Twyce, you have a duplicate value on the primary key. :confused: On which field / fields you have set the primary key ?? Greetings Ralph

[Wendelius]

What is the primary key for registrations table? If it is only student then you'll have a problem since student can have only one module. If the table definition is correct, but you have duplicate data (for example because of denormalization), you can disable the primary key, load the data, clean the data and enable the primary key. Hope this helps, Mika

[Twyce]

the StudentNumber is my primary key

[Twyce]

Ok here is what i am doing.The tble is an asociative entity between table student and table module.so it gets its data from both tables.what i did was jst make the StudentID(from table student) my primary key.A student can be registerd for more than 1 module .i want to have duplicate student numbers but with different module IDs. do you get me?

[Wendelius]

Yep, define both StudentID and ModuleID for the same primary key (multicolumned key)

[perle1]

If you have more then one tables, please give us an illustration. Do you try to insert "Students" twice ?

[Twyce]

thnx ey i'll try that and get back to you

[Twyce]

yes you can say that coz i insert a student number more than once but with a different module code example StudentID ModuleCode s20600752 WIC33222 s20502106 MKI4563 s20600752 LKH7566 s20600752 POU35568 s20502106 KLK67887

[perle1]

:) it will crash on Student s20600752, Line 3 !! this key is already there. set your primary key on both fields "StudentID" and "ModuleCode" and make sure you don't insert a student with the "ModuleCode" twice.

[Twyce]

Thanx for replying so quick. i have jst set my primary key on both fields an ran it and it still gives me the same error.

[perle1]

That make no sense. :doh: You really sure you don't insert different module codes for the same student? Try your routine with fewer data. I hope that will help you to find the problem.

[Mycroft Holmes]

Having used BCP and bulkcopy to move massive amounts of data I can recommend the following. Move your data into a staging table. Manage your transformations by stored procedure AFTER the bulk loading of the data is completed. You have a structural issue, a relational database is NOT a spreadsheet. This data should obviously be goung into 3 or more tables. Student, Course and a manytomany link table. Load the data and then insert/update the student table, update/insert the course table, update/insert the linking table (this has studentid and courseid and data pertaining to this link, scores etc) Twyce, this is database design 101, I recommend you do some study on the proper design of data structures.

Never underestimate the power of human stupidity RAH