impossibility to update data - problem with foreign key

impossibility to update data - problem with foreign key

I started to create a project with a table named SECTION (among others)

I filled data inside and everything was fine.
Then I created a table named SURVEY_VERSION and linked the 2 tables with the mandatory attribute ID_SURVEY_VERSION.
First question: how is the column ID_SURVEY_VERSION filled when I create this mandatory attribute?
Was I not supposed to receive an error message because of the mandatory constraint (not NULL)?

Second question: 
Now I'm in a state where I have one single row in SURVEY_VERSION with id = 2 and I have 3 rows in SECTION with  ID_SURVEY_VERSION = 3. 
How is it possible? ID_SURVEY_VERSION is not supposed to be a foreign key?

3rd question:

I try to update the table SECTION to link all the section to the Survey Version which id is 2.
What is the best way to do that?
I try to open an advanced query in a preparation and launch this request:

but it does nothing. Maybe a bug?
How can I link the 2 tables?
Is there an easy tool to see and edit data?

Thanks for your help

Hi Guillaume,

1st question: You actually do get an exception if you do not fill the foreign key, but only if you set Is Mandatory to Yes. The exception reads something like (for MSSQL) "The INSERT statement conflicted with the FOREIGN KEY constraint "OSFRK_OSUSR_b9d_KHTEST_TEST_MASTER_OSUSR_B9D_KHTEST_TEST_KHTESTTESTID". The conflict occurred in database "MAIN_EMS_EMSO_2013-01-06", table "dbo.OSUSR_B9D_KHTEST_TEST", column 'ID'.
The statement has been terminated.", where the name of the table and database will be different for your case, of course.

2nd question: This depends on the Delete rule. If it is set to "Protect", trying to delete the record that is referenced by the foreign key yields an exception (The DELETE statement conflicted with the REFERENCE constraint "OSFRK_OSUSR_b9d_KHTEST_TEST_MASTER_OSUSR_B9D_KHTEST_TEST_KHTESTTESTID". The conflict occurred in database "MAIN_EMS_EMSO_2013-01-06", table "dbo.OSUSR_B9D_KHTEST_TEST_MASTER", column 'KHTESTTESTID'.
The statement has been terminated.) If it is set to "Ignore" however, you'll end up happiliy with a foreign key that referencing a non-existent record. (The third option, "Delete" will delete the records with foreign key values referencing the to-be-deleted record.)

3rd question: This should work, but only if there is a SURVEY_VERSION with Id 2, unless the Delete rule is "Ignore", in which case it's ok.

Additional question 1: You linked the tables just fine (although I'd choose a different name for the foreign key, e.g. SurveyVersionId), but make sure to set Mandatory to Yes, and Delete rule to Protected.

Additional question 2: Not within the OutSystems platform. So choose your favourite tool (TOAD, MSSQL Mangement Studio, ...). Note that you can always use the Test Query functionality to retrieve data for viewing.
Hi Guillaume

First of all check what is exactly in your table,
the datas are sometimes a little bit different that you expect.

You can use Management studio for that , but a rapid way to do that is to take an action, a preparation for example, and drag the table you want to check (eventually add join and filters) and push "CHECK" button.
For simple tables that helps.

Make an update in a preparation works , but
-Pay attention to the time to do this update, each time you enter in the form the preparation is launched .So it
add some time to the form loading time.
And if the table goes big that can runs into a timeout, giving an error when loading the page.

If that continues , copy the error message , it is sometimes another problem that expected.

Thank you very much for your answer!

Unfortunatly I'm not able to check with management studio yet.

The ID_SURVEY_VERSION parameter is mandatory and protect. So, it cannot be anything other than 2 if I understand.
But I actually think there is a bug with advanced query.
On the top is the result of a simple query. Everything is OK. The foreign key is 2.
But on the bottom is the result of a select * with an advanced query. The foreign key is 3 and points on something that doesn't exist. Plus the section_number is different.
(I launched the 2 requests at the same time)

Hi Guillaume,

That seems a very strange result (especially since SECTION.Section_Number is 0 i/o 5). Instead of "SELECT *", could you specify the specific entity in the advanced query, so "SELECT {SECTION}.*"? Also, you could check the "Executed SQL" tab and compare what's different between the simple and advanced queries with regards to the actual database query the platform produces.
Hi Kilian,
By specify the entity in the advanced query (select {section}.* from {section} instead of select * from {Section}), it worked.
Thank you for your answer.
Probably Service Studio gave you a "Unexpected Advance Query" error, with the message "SELECT statements with '*' cannot be validated against the output structure of 'AdvQuery1' query. Runtime errors might occur." It's in general wise to not ignore these warnings, unless you are exactly sure what you're doing.

The problem with "*" is that "*" returns all columns in the table, which are not necessarily the columns Service Studio expects (or necessarily in the right order). By specifying {TABLENAME}.*, you instruct Service Studio to expand all the columns explicitly in the SELECT list (as can be seen in the "Executed SQL" tab), so that the database returns what Service Studio expects.