Sql Server Default Foreign Key Generation Logic

App Maker apps usually store data in Cloud SQL, a Google Cloud Platform (GCP) service that delivers fully managed SQL databases in the cloud. Learn more about the benefits of Cloud SQL.

Sql Server Default Foreign Key Generation Logical

If defined and indexed correctly, they can be leveraged by the SQL Server query engine to generate extremely efficient query plans. Unlike primary key constraints and unique constraints, foreign key constraints are not automatically indexed by SQL Server. However, indexing the column used in your foreign key is a good idea for a few reasons.

As an app developer, you can choose from two types of Cloud SQL instances:

Default—a G Suite administrator can set up a Cloud SQL instance that is shared among App Maker apps in an organization. When the default instance is set up, a new database is automatically created for your app when you add at least one Cloud SQL data model. Choose this option if your app needs a database that is easy to use and requires no setup. Organizations typically use a default instance while developers prototype and test an app, then switch the app to a custom instance when the app is ready for deployment as a production app.
Custom—Once your admin sets up a default Cloud SQL instance, you can also set up your own Cloud SQL instance. Choose this option when:
- your application serves a large number of users or stores a large amount of data.
- the database must be shared with other applications.
- you need to manage the database or retain control of the Cloud SQL instance.

Note: You can't add a custom Cloud SQL instance until a G Suite admin sets up a default Cloud SQL instance for your organization.

Cloud SQL security

Whether your app uses the Default or a Custom Cloud SQL instance, the credentials are always encrypted in transit and at rest:

Default Cloud SQL–Credentials are stored on the App Maker server. They're never accessible to app developers or end users.
Custom Cloud SQL–Once a developer submits Cloud SQL credentials to App Maker, they're never again visible to the app's developers. App Maker stores the credentials in the browser while a developer works on an app in the editor. The credentials are removed when the developer reloads or closes the browser tab.
When a developer deploys an app with a Custom Cloud SQL model, the credentials are stored on Google servers with the deployed app. They're never accessible to end users.

Before you start

Before you use Cloud SQL with App Maker, review the following security and cost considerations:

Data stored in GCP resides outside of yourG Suite organization—Some organizations, especially government agencies,have strict requirements for storing sensitive data. Both GCP and G Suite meet rigorous privacy and securitystandards, but your use of each of these services may be subject to differentterms. Learn more about how we comply with privacy and security standards:
Cloud SQL is part of GCP—You can start a free trial if you want to try a custom instance. However, continued use incurs expenses for your organization. Learn more about pricing). The typical monthly price for most App Maker apps is negligible.

Use a new custom Cloud SQL instance for your app

If you don't want to use the default Cloud SQL instance, you can use a custom Cloud SQL instance instead. To use a custom Cloud SQL instance, a G Suite admin must still set up a default instance first. Your app can use an existing custom database, or you can create a new one. If you create a new custom instance, it takes some time to set it up and integrate it with App Maker.

Note: You can't connect your app to a database in a custom Cloud SQL instance unless your G Suite admin sets up a default Cloud SQL instance.

We recommend that you use a Second Generation Cloud SQL instance if you're setting up a new one. App Maker also supports First Generation instances. Pricing and performance characteristics vary depending on the type of instance, so review capabilities before you create an instance.

Note: App Maker supports only MySQL databases. You can't use PostgreSQL.

The following instructions require that you have:

Access to the GCP Console.
Permissions to create Cloud SQL instances and users.

Second Generation

Sql Server Default Foreign Key Generation Logical

If you haven't already, create a Second Generation Cloud SQL for MySQL instance in the us-central region.
Note: When you configure the instance:
- You must select MySQL as the database type.
- We recommend that you use the us-central1 region for your new instance, regardless of your geographic location. You can select a different region, but app performance might not be satisfactory because apps are run in us-central.
In the GCP Console, create a MySQL user account for your app to use to access the database.
Record the username and password so that you can enter them when you connect your app to the database. To prevent unauthorized access through user accounts, we recommend that you set strong passwords for all accounts on the instance.
In the GCP Console, create a new database.
App Maker runs on App Engine. Set up a connection with App Engine by adding a service account to your Cloud project:
1. Go to the IAM & Admin Projects Page (in the GCP Console, click Menu menuchevron_rightIAM & Admin).
2. Select the project that contains your Cloud SQL instance.
3. Click person_addAdd.
4. In the New members dialog, enter:
  appmaker-maestro@appspot.gserviceaccount.com
  Choose Cloud SQLchevron_rightCloud SQL Client as the role.
5. Click Save.
6. If other developers will edit or deploy this app, add their Google accounts as new members and assign the Cloud SQL Client role.
Copy the details of your instance:
1. Open the Cloud SQL instances page (in the GCP Console, click Menu menuchevron_rightSQL).
2. Click your instance and look for the Instance connection name field.
3. In the Instance connection name box, click Copy content_copy.
Set your app to use the custom Cloud SQL database:
1. In App Maker, open your app and click Settings settingschevron_rightDatabase.
2. Click Switch to a custom Cloud SQL database.
3. Paste the instance details, and then add the name of the database you created in Step 3. Use the following format:
  projectName:regionName:instanceName/databaseName
4. Enter the username and password for the database user account, then click Continue.

First Generation

App Maker supports First Generation instances. However, if you're creating a new instance, we recommend that you create a Second Generation instance instead.

If you haven't already, create a First Generation instance.
Note: When you configure the instance:
- You must select MySQL as the database type.
- We recommend that you use the us-central1 region for your new instance, regardless of your geographic location. You can select a different region, but app performance might not be satisfactory because apps are run in us-central.
Configure user accounts for this instance:
- Change the password for the root account. To prevent unauthorized access through the App Engine connection (created in the following steps), we recommend that you set a strong password for the account.
- Create a user account that your app can use to access the database. When you create the database user:
  - Specify localhost as the Host Name.
  - Set a strong password for the account to prevent unauthorized access.
  Record the username and password so that you can enter them when you connect your app to the database.
In the GCP Console, create a new database.
App Maker runs on App Engine. Set up a connection with App Engine by adding App Maker's application ID to your Cloud SQL instance:
1. Go to the Cloud SQL instances page (in the GCP Console, click Menu menuchevron_rightSQL).
2. Click the instance name to open its Overview page, and then click the Authorization tab.
3. Click Add project ID and enter:
  appmaker-maestro
4. Click Done to exit edit mode.
5. Click Save to update the instance.
Add the custom Cloud SQL instance to your app.
1. Open your app and click Settings settingschevron_rightDatabase
2. Click Switch to a custom Cloud SQL database.
3. Enter the address in the following format:
  projectName:instanceName/databaseName
4. Enter the username and password for the database user account, then click Continue.

Use an existing Cloud SQL database for your app

Add the instance details to your app:
1. Open the Cloud SQL instances page (in the GCP Console, click Menu menuchevron_rightSQL).
2. Click your instance and look for the Instance connection name field.
3. In the Instance connection name box, click Copy content_copy.
4. In App Maker, open your app and click Settings settingschevron_rightDatabase.
5. Click Switch to a custom Cloud SQL database.
6. Paste the instance details, and then add the name of the existing database. Use the following format: projectName:regionName:instanceName/databaseName
7. Enter the username and password for the database user account, then click Continue.
In the App Maker UI, next to Data click Addadd.
Select Google Cloud SQL (existing).
Enter the address in the following format:
- First generation—projectName:instanceName/databaseName
- Second generation—projectName:regionName:instanceName/databaseName
Enter the username and password for the database user account.
Select a table from the list and click Import. You can import any table that's not a join table.

App Maker automatically creates relations once you import both tables involved in a relation. App Maker recognizes relations in a Cloud SQL database using join tables or foreign key constraints in imported tables. For example, if table A has a foreign key to a table B, App Maker automatically creates a many-to-one relation from A to B.

If the schema in the Cloud SQL database changes, you can update your models:

Click Settings settingschevron_rightDatabase.
Under Model and relation compatibility click the Check button.You have options to update all Cloud SQL models to match the database or to update the database to match the models.

If you update models, App Maker preserves changes to the model like modified field names, validation properties, and events.However, if a field is deleted from Cloud SQL, the corresponding field is also deleted from the model.

Update Cloud SQL settings for your app

You can go to Settings settingschevron_rightDatabase to:

Switch to the default SQL instance.
Update the instance address.
Check model and relation compatibility.

Switch between default and custom Cloud SQL databases

For organizations that have set up a default Cloud SQL instance, new models use this instance. The default Cloud SQL database is ideal if your app needs a database that requires no set up and is easy to use.

If you find the default database doesn't meet your needs, you can convert it to a custom database:

If you haven't already, set up a custom Cloud SQL instance. Choose us-central as the region for your new instance.
Open your app and click Settings settingschevron_rightDatabase.
Click the Check button to verify model and relationship compatibility. If App Maker reports incompatibilities, you might not be able to proceed without deleting data from your database.
When you're ready to proceed, click the Switch to a custom Cloud SQL database button.
Enter the address in the following format:
- First generation—projectName:instanceName/databaseName
- Second generation—projectName:regionName:instanceName/databaseName
Enter the username and password for the database user account, then click Continue.

After switching to a custom database, you have the option of switching back to a default database.

Work with Cloud SQL records

Records created on the server using newRecord() have a null key until they're saved, even if the primary key column is assigned.
Draft records on the client have synthetic keys that don't match the primary key column until they're saved.
Records created on the client in a manual-save-mode datasource have synthetic keys that don't match the primary key column even after they're saved.

You have several options for handling primary keys in App Maker:

Relations

Fields that correspond to foreign key fields can be read directly using server scripts. On the client, the values of the foreign key fields and their corresponding associations can be modified as relations. Learn more about modifying relations.

Views

You can import Cloud SQL views as read-only data models. Models based on views can take advantage of SQL queries that involve joins, aggregations, and sub-selects. For example, the following schema uses a view to aggregate data from a table for easy display in App Maker:

Calculated SQL Models

For time-based parameters, you need to set the time zone.

Note: Calculated SQL models don't inherit or respect the security restrictions set for the queried models. Set security restrictions for the calculated SQL model itself in its Security tab.

Relation filtering

Cloud SQL is the only model type that can be filtered by a field in a related model in Data Binding and Server Scripting.

Learn more about relation filtering.

Time zones

In some situations, a Cloud SQL instance and App Maker are in different time zones. This can cause undesired dates and times to be written to the database. Review the following table and ensure that you set your date field types appropriately.

Time zone handling for date field types
Cloud SQL models	Set the date field type as required: Open the Fields tab for a model. Click a date field, then click keyboard_arrow_downAdvanced to set the date type: DATE or DATETIME—Use for fields that don't have a time zone. App Maker converts the time to the time zone of the App Maker server (as set in App Settings) before writing it to the database. TIMESTAMP—Use for fields that have a time zone. App Maker saves the user's time zone and the date is written in Cloud SQL relative to the Unix epoch. For both date types, App Maker converts the date to the browser's time zone when it displays the data.
Calculated SQL models	When you add a Date-type field to a calculated SQL model, it is added as a DATETIME type. Note: We recommend that you don't set your datasource to query a TIMESTAMP field in a regular cloud SQL model for date values. The date is read correctly only if the app and the database are in the same time zone.
Calculated SQL datasource query parameters	Set the date field type as required: Open the Datasources tab for the calculated SQL model. Click the datasource, then click addAdd Parameter. For date parameters, click the Data SQL Type drop-down list and select a type: DATE or DATETIME—Use for parameters that don't have a time zone. When the query uses a DATE or DATETIME field, the App Maker server converts the value to the server's time zone from the app user's time zone before it is used in the query. For example, if a user in PST (UTC–8) queries for records that were created before January 1, 2018 at 9 am and the App Maker server is in CT (UTC–6), then the query searches for records that were created before January 1, 2018 at 11 am. TIMESTAMP—Use for parameters that have a time zone. App Maker converts the time to UTC before it is used in the query. We recommend that you don't use TIMESTAMP-type query parameters because the date is handled correctly only if the app and the database are in the same time zone.

Time zone handling for date field types

Cloud SQL models

Set the date field type as required:

Open the Fields tab for a model.
Click a date field, then click keyboard_arrow_downAdvanced to set the date type:
- DATE or DATETIME—Use for fields that don't have a time zone. App Maker converts the time to the time zone of the App Maker server (as set in App Settings) before writing it to the database.
- TIMESTAMP—Use for fields that have a time zone. App Maker saves the user's time zone and the date is written in Cloud SQL relative to the Unix epoch.

For both date types, App Maker converts the date to the browser's time zone when it displays the data.

Calculated SQL models

When you add a Date-type field to a calculated SQL model, it is added as a DATETIME type. Note: We recommend that you don't set your datasource to query a TIMESTAMP field in a regular cloud SQL model for date values. The date is read correctly only if the app and the database are in the same time zone.

Calculated SQL datasource query parameters

Set the date field type as required:

Open the Datasources tab for the calculated SQL model.
Click the datasource, then click addAdd Parameter.
For date parameters, click the Data SQL Type drop-down list and select a type:
- DATE or DATETIME—Use for parameters that don't have a time zone. When the query uses a DATE or DATETIME field, the App Maker server converts the value to the server's time zone from the app user's time zone before it is used in the query.
  For example, if a user in PST (UTC–8) queries for records that were created before January 1, 2018 at 9 am and the App Maker server is in CT (UTC–6), then the query searches for records that were created before January 1, 2018 at 11 am.
- TIMESTAMP—Use for parameters that have a time zone. App Maker converts the time to UTC before it is used in the query. We recommend that you don't use TIMESTAMP-type query parameters because the date is handled correctly only if the app and the database are in the same time zone.

Restrictions and limitations

If your Cloud SQL tables have zero or multiple primary key columns, App Maker can't modify or make relations for them.
App Maker represents BIGINT values as strings, because the number range represented by BIGINT is too large to be represented with JavaScript numbers.
App Maker can't import some Cloud SQL column types like BLOB, CLOB, and other binary types. You can still import models with those column types, but these columns won't be readable or writable. If these columns are marked non-null, then all other columns in the entire data model will be read-only.
App Maker can't express queries against Cloud SQL models that involve joins, aggregations, or sub-selects. Instead, import a SQL view as a model or use a SQL datasource in a calculated model.
App doesn't support searches by keyword. As a workaround, create a custom query that simulates a full-text search using contains operators. For example, for a model Employee with fields Name and Department, use the query: (Name contains? :Keywords) OR (Department contains? :Keywords) and bind the Keywords parameter to a text field's value property.