by David Crowther
Question:
Is it possible to create default values in my SQL Database tables?
Answer:
Yes, this is a very common and very useful function that you can apply at database level.
In this example we have a Planning Applications layer being shown in our webGIS – MapThat – and the user can use the Drawing Tools to create new features and enter the required attributes.
The SQL Table behind this spatial layer has lots more columns of data that we haven’t exposed to the users –
Database Triggers –
This could include some fields that you may want your database to auto populate when you create and also edit records e.g. the AREA, Centroid X and Y fields… and these can be auto updated using Database Triggers!
These triggers can be applied to your table on INSERT, DELETE, MODIFY actions and can ensure your data is updated automatically. For example if a user reshapes a planning application, the trigger can work on this MODIFY action and re-calculate and then update the new shapes AREA without the user needing to do it.
Here is a link to another FAQ to learn more about Database Triggers - https://www.cadlinecommunity.co.uk/hc/en-us/articles/360001505997-MapThat-Linking-to-Google-StreetView-and-Database-Triggers
Default Values -
In our example, we are going to utilise a DEFAULT VALUE for a field in our table only when a new Record is INSERTED. This value will not change if records are modified – as a trigger would do – but can be very useful to enforce default and common values into your data so that you minimise empty or NULL values.
The field we will create a DEAFULT for is the Date_Created field.
As I haven’t associated a Default value yet, you can see that the previous records in the table have NULL values. However, moving forward I would like to capture the Date that the new records were created or INSERTED.
To create a Default Value, you can do this in the Database Table > Design mode –
or you can use a SQL Query command to create the Default Constraint manually –
USE [LCC_GEOSTORE]
GO
ALTER TABLE [dbo].[lcc_planning_apps] ADD CONSTRAINT [DF_lcc_planning_apps_Date_Created] DEFAULT (getdate()) FOR [Date_Created]
GO
The above query is:
- altering the lcc_planning_apps table
- adding a Constraint – called DF_lcc_planning_apps_Date_Created
- and defining the Constraint to be a DEFAULT value on the Date_Created field
- with the DEFAULT value not being a fixed text value – but a variable called getdate
Once the query is ran, a new CONSTRAINT is created on the SQL table.
If we now add a new Planning Application record, here via MapThat –
If we now check the inserted record, we can see that the Database Constraint has automatically added a DEFAULT value of today’s date (getdate) into the Date_Created field.
Why not try adding database Constraints and Default values to your important columns to ensure you minimise incorrect or NULL values in your SQL Tables.