OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Error Message when building table relations

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Mere1
Newbie
Newbie


Joined: 22 Feb 2012
Posts: 2

PostPosted: Wed Feb 22, 2012 11:39 am    Post subject: Error Message when building table relations Reply with quote

Hello - rather new to this so go slowly ! Whilst working on OOO 3.3 on Windows 7 I have a number of simple tables for a database, pupils, parents, schools etc. When I try and make a relationship between two tables I get the following error message:




SQL Status: S0021
Error code: -57

Column types do not match in statement [ALTER TABLE "Pupils" ADD FOREIGN KEY ("SChool ID") REFERENCES "Schools" ("School ID")]


I have made sure the field types are the same. I deleted the programme and reinstalled it. I tried it with other tables but I got the same error code. Could anyone help me ?
Many thanks in advance
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Feb 22, 2012 11:53 am    Post subject: Reply with quote

I suspect the error is because the field name . . . as you the error message displayed "SChool ID" must match the table definition names, exactly as it is defined in your table . . . including . . . CASE ( UPPER / Mixed / lower ) .

For example, a field name of "SChool ID" is NOT the same as a field name of "School ID", nor, "school ID" . So, make sure the letters between the double quotes are entered EXACTLY as defined in your table definition, including CASE. Smile

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Mere1
Newbie
Newbie


Joined: 22 Feb 2012
Posts: 2

PostPosted: Wed Feb 22, 2012 12:03 pm    Post subject: Reply with quote

Thanks so much for replying. Sadly this occurs with all the tables, so if I do 'sport' or 'team' the same thing happens. But that's a really interesting point, I didn't know you had to have the same fields if making relations.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Feb 22, 2012 12:30 pm    Post subject: Reply with quote

Mere1 wrote:
you had to have the same fields if making relations.


No, you do NOT have the same fields in the two tables, BUT, yes, the spelling of the fields must be exactly as defined in your tables, including CASE ( UPPER / Mixed / lower ). Smile

Two more things to consider:

  1. "Schools"."School ID" MUST be the Primary Key for the table "Schools" . Is it?

  2. A Foreign Key can only be added, if, at the current time ( database pun intended Smile ), with all of the values currently in your table "Pupils" , there would be NO violations of the FOREIGN KEY rule. Put another way, , . at the current time any values of NULL ( undefined ) in "Pupils"."SChool ID" ? ? ? Or, any value at the current time in "Pupils"."SChool ID" that are not currently in "Schools"."School ID" ? ? ?

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group