SQL HELP:

[seadude]

Hey everyone, @wyotim, this is your area of expertise isn't it?

My first time SQL app and am having a challenge:

• 5 tables. Each with a Primary Key
• Foreign Key relationships between tables as shown below
• 
• The issue:
  • When I Patch the readingTable, which has a Foreign Key dependency on the dateTable, I get the error:
  • "The INSERT statement conflicted with the FOREIGN KEY constraint named "FK_reading_to_dateTable". The error occured on database-x, table-y, column "dateId".
• The problem appears to be that I'm patching the readingTable BEFORE the dateTable has any data in it.
• I made the dateId column in the readingTable allow null, but still wouldn't Patch.

How do I Patch a table that has a foreign key dependency on another table that has not been patched yet?

What am I missing?

Thank you

[wyotim]

Nicely done! One small suggestion: be sure to watch any sorting when using Last() as you may find you get the wrong FK if you sort by another column. I only say that because I seem to always be sorting by something other than the PK. Max() can be nice in those cases if you are using an integer for your PK.

Again, well done and, as always, a pleasure to work with you!

[wyotim]

One last thing: views are indeed read-only, but man are they a great tool. If you have any data that references multiple tables, it can really simplify things. I highly recommend checking them out!

[seadude]

Max() sounds like the way to go as all the PK's are INT. Sound advice!

[seadude]

Hm. Can't seem to substitute Last() for Max(). 

Tried: Max(tableName.columnName), Max(tableName).columnName, Max(Value(tableName.columnName)), Max(First(tableName.columnName)),

Errors out with either:

"Invalid argument type (Table) expecting a number instead."
"Invalid argument type (Record) expecting a number instead."

[wyotim]

Maybe try:

Max(tableName, columnName)

 I think that is the syntax but let me know if it doesn't work.

[seadude]

And...yeah. Breath in... Calm the mind... Focus.... Breath out... ahhhhhhh.

🙂 Yeep that was it 🙂

[wyotim]

Good deal! And good mantra for this Friday (as well as PowerApps development in general).  

[seadude]

On the SQL View point... do you have an approach you use to craft Views? I don't quite know where to start. Beginning with the syntax was not the right place. I've heard to "start with the query you want". But i'm not sure what this truly means.
Since this is my first SQL rodeo, i'm more focused on just getting the app to functionally work. Maybe I have to go through the lumps of wiring it all up to truly appreciate and understand the View piece.

[wyotim · Accepted solution]

Apologies for the late reply!

What helped me the most with views was actually making reports in Power BI. Something about having Power Query to mash up data plus the model view so you can visualize the relationships really helped (and continues to help).

I agree with the “start with the query you want” advice but my variation on that would be to map out the resultant table you want. From their, I think about the main data point(s) I am after and how they relate to the other data needed to create the view due to PK/FK relationships, and then what kind of filtering I will want before and after the view is made (i.e. do I want to filter by an FK or date before the view is made to limit the size/scope of the created view and/or do I want to have the ability to filter by something that isn’t in the view currently and needs to be added for that purpose). Sometimes it is more straightforward and others it can be a bit tricky but I do think just jumping in and smashing data sets together is a great way to learn.

I also use SSMS, which has a pretty great way to make views. I assume most people use that too but it is worth mentioning just in case you aren’t.

I would be happy to post some examples of the views I have made of that would be helpful but I won’t be able to do so until late this week or early next. I am out of the office due to a (pre-planned) medical event for my wife. If I can expound on anything, let me know and I will be happy to do so!

[seadude]

Nice advice. Thank you.

When the time is right, lets create another thread for "SQL View Best Practices" or the like.

Take care