Integrity constraint violation

[sage703]

I'm getting the following error when I try to insert the following code:

Code:

<cfquery datasource="#data_source#">
INSERT INTO Healthcare_Catalog(
Program_Name,
Program_Web,
Program_Description,
Provider_Name_Short,
School,
Location_City,
Location_State,
Program_Notes,
Program_Contact,
Contact_Email,
Contact_Phone,
Distance,
Degree_Level,
Attendance_Options,
Length_of_Program,
Length_of_Program_Years,
Occupational_Information,
Rural_Opportunities,
Admission_Prerequisites)
VALUES(
'#Trim(SESSION.ProgWiz.Program_Name)#',
'#Trim(SESSION.ProgWiz.Program_Web)#',
'#Trim(SESSION.ProgWiz.Program_Description)#',
'#Trim(SESSION.ProgWiz.Provider_Name_Short)#',
#Trim(SESSION.ProgWiz.School)#,
'#Trim(SESSION.ProgWiz.Location_City)#',
'#Trim(SESSION.ProgWiz.Location_State)#',
'#Trim(SESSION.ProgWiz.Program_Notes)#',
'#Trim(SESSION.ProgWiz.Program_Contact)#',
'#Trim(SESSION.ProgWiz.Contact_Email)#',
'#Trim(SESSION.ProgWiz.Contact_Phone)#',
'#Trim(SESSION.ProgWiz.Distance)#',
'#Trim(SESSION.ProgWiz.Degree_Level)#',
'#Trim(SESSION.ProgWiz.Attendance_Options)#',
'#Trim(SESSION.ProgWiz.Length_of_Program)#',
#Trim(SESSION.ProgWiz.Length_of_Program_Years)#,
'#Trim(SESSION.ProgWiz.Occupational_Information)#' ,
'#Trim(SESSION.ProgWiz.Rural_Opportunities)#',
'#Trim(SESSION.ProgWiz.Admission_Prerequisites)#')
</cfquery>


Error:

ODBC Error Code = 23000 (Integrity constraint violation)

[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the
table were not successful because they would create duplicate values in the
index, primary key, or relationship. Change the data in the field or fields
that contain duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again.

SQL = "INSERT INTO Healthcare_Catalog( Program_Name, Program_Web,
Program_Description, Provider_Name_Short, School, Location_City,
Location_State, Program_Notes, Program_Contact, Contact_Email, Contact_Phone,
Distance, Degree_Level, Attendance_Options, Length_of_Program,
Length_of_Program_Years, Occupational_Information, Rural_Opportunities,
Admission_Prerequisites) VALUES( 'TESTONE', 'blah', 'blah', 'ACNM', 35, 'blah',
'AK', 'blah', 'blah', 'blah', 'blah', 'Didatic Only', 'AA', 'Full Time', '<
year (0-10 days)', 5, 'blah', 'blah', 'blah')"

Data Source = "AKHC"

The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (3:2) to (3:37) in the template file
D:\Inetpub\dist-web\users\sagea\health_careers\sessions\program_in sert.cfm.


The only primary key is the Program_Name field, which I am definately giving a
new value. The foreign keys are Provider_Name_Short and School, but both of
those values are allowed to have duplicate values. Nothing else is required.
Any suggestions?

S./

[mxstu]

If you're using actual FK (Foreign Keys), do the database relationships
"Enforce Referential Integrity"? If so, then the keys Provider_Name_Short =
'ACNM' and the School ID = 35 must exist in the related tables before you
INSERT those values into the [Healthcare_Catalog] table.

Also, if you're going to be spending a good deal of time working with queries
and databases, you may want to pick up a good SQL book.

[sage703]

Hello mxstu,

I know, I need a good CF/SQL book...got any good recommendations?

As for this particular problem, I did manage to work it out on my own
(temporarily). If I fill in all the fields, as you suggested, making sure every
field has a value, it works! I'll have to make sure certain fields don't have
to get values in order for this to work like i want. As for the foreign keys,
I already was using extant fields from those other tables because I was taking
those values from <cfselect> fields that acquire their values from those
foreign keys.

Thanks for your help!
S./

[mxstu]

Just noticed this post... I can't really recommend anything that's current,
and since CF MX introduced a lot of changes, some of the older books might do
you more harm than good ;-) I would search the forums (either here or through
Google Groups) and you'll probably find some good recommendations.

Yes, setting the "Allow Zero Length" String setting to no, for optional
fields, would solve that problem.