How the Different Types of Database Constraints?

Database constraints are a key feature of database management systems. They ensure that rules defined at data model creation are enforced when the data is manipulated ( inserted, updated, or deleted) in a database.

Constraints allow us to rely on the database to ensure integrity, accuracy, and reliability of the data stored in it. They are different from validations or controls we define at application or presentation layers; nor do they depend on the experience or knowledge of the users interacting with the system.

In this article, we will briefly explain how to define the following types of constraint and their usage:

Why Do We Need to Define Constraints in a Database Model?

Although it is not mandatory, defining constraints ensures at database level that the data is accurate and reliable. Having those rules enforced at database level rather than in the application or presentation layer ensures that the rules are consistently enforced no matter who manipulates the data and how it is manipulated. When a customer uses a mobile or web application, when an employee uses an internal application, or when a DBA executes data manipulation scripts, any changes are evaluated at the database level to guarantee that no constraint is violated.

Are Database Constraints the Same in all RDBMSs?

Not all database management systems support the same types of constraints. When they do, there may be special features or considerations for the specific system. The syntax for creating constraints also depends on the specific RDBMS.

Constraints can be defined when we create a table or can be added later. They can be explicitly named when created (thus allowing us to identify them easily), or they can have system-generated names if an explicit name is omitted.

For the examples in this article, we are going to use Microsoft SQL Server as the platform to show the syntax of adding named constraints to an existing table. We will be starting with a basic table named Product with the following attributes:

Note:

You can also read the article “CONSTRAINTS IN POSTGRESQL AND HOW TO MODEL THEM IN VERTABELO” if you want to learn the specifics of PostgreSQL constraints.

Constraint Types

Now, let’s review the different constraint types we can find in most database engines. We are going to start from the most basic then move on to the more complex.

DEFAULT

This type of constraint allows us to define a value to be used for a given column when no data is provided at insert time. If a column with a DEFAULT constraint is omitted in the INSERT statement, then the database will automatically use the defined value and assign it to the column (if there is no DEFAULT defined and the column is omitted, the database will assign a NULL value for it). Defaults can be fixed values or calls to system-provided or user-defined SQL functions.

Let’s look at our example data model. We will start by defining a couple of DEFAULT constraints for our Product table:

For the column EntryDate, we will use GETDATE(), a system function that returns the current date.
For the column CurrentStock, we will use a fixed value of 0.

We need to issue the following two statements in SQL Server to create these constraints:

ALTER TABLE Product ADD CONSTRAINT DF_Product_EntryDate

DEFAULT GETDATE() FOR EntryDate;

ALTER TABLE Product ADD CONSTRAINT DF_Product_CurrentStock

DEFAULT 0 FOR CurrentStock;

And we can see how they are defined using the VERTABELO DATA MODELER tool:

Once the DEFAULT is created for a column, we can insert a row in our table without specifying the column:

INSERT INTO Product (ProductCode, ProductName, Price)

VALUES (‘S10’, ‘Spoon’, 120.50);

A new row will be inserted, the EntryDate column will have today’s date stored for this row, and the CurrentStock column will have the initial value of 0.

Extra Tip #1

Most database engines (like Oracle, SQL Server, DB2, and MySQL) allow the DEFAULT to be explicitly included in INSERT statements, making it clear that the value to be used is the one defined in the constraint rather than omitting the column(s) in question:

INSERT INTO Product (ProductCode, ProductName, Price, CurrentStock, EntryDate)

VALUES (‘S10’, ‘Spoon’, 120.50, DEFAULT, DEFAULT);

Extra Tip #2

Most database engines (like Oracle, SQL Server, DB2, and MySQL) also allow the value to be used in UPDATE statements. If there is a DEFAULT defined, you can use the following syntax to set the column to the DEFAULT:

UPDATE Product SET CurrentStock = DEFAULT;

CHECK

CHECK constraints allow us to define a logical condition that will generate an error if it returns FALSE. Every time a row is inserted or updated, the condition is automatically checked, and an error is generated if the condition is false. The condition can be an expression evaluating one or more columns. It can also include hardcoded values, system-provided functions, or user-defined functions.

Now, we are going to define a couple of CHECK constraints for our table, so that we:

Do not allow the CurrentStock column to store negative values.
Do not allow the Price column to store zero or negative values.

To do so, we need to execute the following statements:

ALTER TABLE Product ADD CONSTRAINT CK_Product_CurrentStock

CHECK (CurrentStock >= 0);

ALTER TABLE Product ADD CONSTRAINT CK_Product_Price

CHECK (Price > 0);

The VERTABELO DATA MODELER tool allows us to define CHECK constraints easily:

Extra Tip

CHECK constraints return an error only when the condition evaluates to FALSE. Be sure to consider handling scenarios where the CHECK condition may return a NULL, since the database would not consider that an error. For example, this UPDATE statement will not return an error:

UPDATE Product SET CurrentStock = NULL;

By default, all columns in a table accept NULL values. A NOT NULL constraint prevents a column from accepting NULL values. Unlike other constraints, NOT NULL constraints cannot be created with a name, and they require a different syntax when we want to add them to an existing table.

We will continue enhancing our model, modifying our Product table so that all columns except Notes have a NOT NULL constraint in place by executing the following statement:

ALTER TABLE Product

MODIFY ProductCode VARCHAR(20) NOT NULL,

ProductName VARCHAR(100) NOT NULL,

Price MONEY NOT NULL,

CurrentStock INT NOT NULL,

EntryDate DATE NOT NULL;

Columns are defined to be NOT NULL by default in VERTABELO DATA MODELER, but you can easily change a column behavior by selecting or deselecting the N[ull] checkbox in the column definition:

Extra Tip #1

Some databases implement the NOT NULL constraint as a special class of the CHECK constraint, with the condition to be checked internally generated as “<ColumnName> IS NOT NULL”. This does not change how the NOT NULL is defined, just how it is handled internally by the RDBMS.

Extra Tip #2

Review your data model and ensure that you do not accept NULL in columns that should not be NULL. This will save you time when debugging errors or issues in the future. It may also have performance impact, since the database engine may use different execution plans depending on whether or not a column has NULL values.

UNIQUE KEY

Unique keys are defined at table level and can include one or more columns. They guarantee that values in a row do not repeat in another. You can create as many unique keys as you need in each table to ensure that all business rules associated with uniqueness are enforced.

We are going to add a couple of unique keys to our Product table to ensure we do not allow duplicate values in the following two columns:

ProductCode. Since we use this value to identify a product, we should not accept duplicate values.
ProductName. Since this is the description shown when searching for products, we need to be sure that we do not have two products with the same value.

To create those two constraints, we execute the following statements:

ALTER TABLE Product ADD CONSTRAINT UK_Product_ProductCode

UNIQUE (ProductCode);

ALTER TABLE Product ADD CONSTRAINT UK_Product_ProductName

UNIQUE (ProductName);

VERTABELO DATA MODELER allows you to define any unique key in a couple of simple steps:

Extra Tip

Most RDBMSs implement unique keys by using an index to speed up searching for duplicates when a row is inserted or updated. Also, most RDBMSs will automatically create a unique index when a unique key is added. However, you can elect to use an already existing index if one is available.

PRIMARY KEY

A primary key is a constraint defined at table level and can be composed of one or more columns. Each table can have only one primary key defined, which guarantees two things at row level:

The combination of the values of the columns that are part of the primary key is unique.
All the columns that are part of the primary key have non-null

So, we can consider the primary key as a combination of the NOT NULL and UNIQUE constraints.

Continuing with our example, we are now going to add a primary key to our table, first adding a ProductID column that will act as a surrogate primary key (please read the article “WHAT IS A SURROGATE KEY?” to learn about surrogate keys and the differences from natural primary keys) and then adding the primary key. The syntax to add the constraint is:

ALTER TABLE Product ADD CONSTRAINT PK_Product PRIMARY KEY (ProductID);

When we verify our model, we can see that the column ProductID does not accept NULL and is identified as part of the new primary key in the table:

Extra Tip #1

Usually, the columns that are part of the primary key are the ones that are referenced by the foreign keys in child tables (we will explain this a little later in this article). Like any other unique keys, primary keys can be created on a single column or on a set of columns. Choosing the right column or columns for each primary key is a critical task when creating a data model. This topic is discussed in various articles we mention throughout here.

Extra Tip #2

When using surrogate primary keys, we can take advantage of built-in features like IDENTITY to populate the column created for the primary key. But if we use GUID (UniqueIdentifier) or another data type, we can also consider adding a DEFAULT constraint to the primary key column that includes a function like NEWID() to populate the column with system-generated values.

FOREIGN KEY

Foreign keys are vital to maintaining referential integrity in a database. They guarantee that each row in a child table (like Order) has one and only one row associated in a parent table (like Product). Foreign keys are created in child tables, and they “reference” a parent table. To be able to reference a table, a constraint that ensures uniqueness (either a UNIQUE or PRIMARY KEY) must exist for the referenced columns of the parent table.

When a foreign key is defined, the two tables become related, and the database engine will ensure that:

Every value or combination of values entered at INSERT or UPDATE in the columns that are part of a foreign key exist exactly once in the parent table. This means that we cannot insert or update a row in the Order table with a reference to a product that does not exist in the Product
Every time we try to DELETE a row in the parent table, the database will verify that it does not have child rows associated; the DELETE will fail if it does. This means that we would not be able to remove a row in Product if it has one or more related rows in the Order

In our example model, we have created two child tables named PurchaseDetail and OrderDetail, and we need them to reference the existing Product table. Since foreign keys can reference either primary keys (the most common scenario) or unique keys, we are going to use the ProductID column (which is defined as PRIMARY KEY) as reference for our OrderDetail table. However, we will use ProductCode (which is defined as a unique key) as the reference for the PurchaseDetail table. To create the constraints, we need to execute the following two statements:

ALTER TABLE OrderDetail ADD CONSTRAINT FK_OrderDetail_ProductID

    FOREIGN KEY (ProductID) REFERENCES Product (ProductID);

ALTER TABLE PurchaseDetail ADD CONSTRAINT FK_PurchaseDetail_ProductCode

    FOREIGN KEY (ProductCode) REFERENCES Product (ProductCode);

Foreign keys can be created easily in VERTABELO DATA MODELER by relating the parent table to the child table then confirming the columns that define the relationship:

Extra Tip

A foreign key can ensure that a child row points to an existing parent row and also that a parent row is not deleted if it has child rows. There are additional behaviors discussed in the article “ON DELETE RESTRICT VS NO ACTION.” You may want to read this to take full advantage of the foreign key features.

How Database Constraints Are Classified

Constraints are usually either column-level or table-level, and the classification depends on the sections of the CREATE TABLE statement in which they can be defined. Reviewing the CREATE TABLE syntax, we can easily identify those places:

CREATE TABLE table_name (

   column1 datatype column_level_constraint1 column_level_constraint2,

   column2 datatype column_level_constraint3,

   table_level_constraint1,

   table_level_constraint2

);

All constraint types we have reviewed can be defined at column level as long as they involve only a single column (the column that is being defined). All constraint types except NOT NULL can also be defined at table level, and this is mandatory when a constraint involves more than one column (like complex CHECK conditions and multiple-column unique, primary, or foreign keys). DEFAULT constraints can involve only one column, but they can be defined at either level.

Constraint Type	Table Level	Column Level
DEFAULT	Yes (only one column)	Yes (only one column)
CHECK	Yes (multiple columns)	Yes (only one column)
NOT NULL	No	Yes (only one column)
UNIQUE	Yes (multiple columns)	Yes (only one column)
FOREIGN KEY	Yes (multiple columns)	Yes (only one column)
PRIMARY KEY	Yes (multiple columns)	Yes (only one column)

Database Constraints: What Next?

We have reviewed the six types of constraints available in most RDBMSs and taken a quick look at how to create them using the Microsoft SQL Server syntax. We have also seen examples using VERTABELO DATABASE MODELER as our modeling tool. If you want to learn in depth how to create and maintain constraints using Vertabelo, you should follow this up with reading the article “DATABASE CONSTRAINTS: WHAT THEY ARE AND HOW TO DEFINE THEM IN VERTABELO”.

Original article source at: https://www.vertabelo.com/

#database #types

What is GEEK

Buddha Community

How the Different Types of Database Constraints?

Ruth Nabimanya

1620633584

System Databases in SQL Server

Introduction

In SSMS, we many of may noticed System Databases under the Database Folder. But how many of us knows its purpose?. In this article lets discuss about the System Databases in SQL Server.

System Database

Fig. 1 System Databases

There are five system databases, these databases are created while installing SQL Server.

Master
Model
MSDB
Tempdb
Resource

Master

This database contains all the System level Information in SQL Server. The Information in form of Meta data.
Because of this master database, we are able to access the SQL Server (On premise SQL Server)

Model

This database is used as a template for new databases.
Whenever a new database is created, initially a copy of model database is what created as new database.

MSDB

This database is where a service called SQL Server Agent stores its data.
SQL server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts.

TempDB

The Tempdb is where SQL Server stores temporary data such as work tables, sort space, row versioning information and etc.
User can create their own version of temporary tables and those are stored in Tempdb.
But this database is destroyed and recreated every time when we restart the instance of SQL Server.

Resource

The resource database is a hidden, read only database that holds the definitions of all system objects.
When we query system object in a database, they appear to reside in the sys schema of the local database, but in actually their definitions reside in the resource db.

#sql server #master system database #model system database #msdb system database #sql server system databases #ssms #system database #system databases in sql server #tempdb system database

Django Packages

1640257440

Django-allauth: A simple Boilerplate to Setup Authentication

Django-Authentication

A simple Boilerplate to Setup Authentication using Django-allauth, with a custom template for login and registration using django-crispy-forms.

Getting Started

Prerequisites

Python 3.8.6 or higher

Project setup

# clone the repo
$ git clone https://github.com/yezz123/Django-Authentication

# move to the project folder
$ cd Django-Authentication

Creating virtual environment

Create a virtual environment for this project:

# creating pipenv environment for python 3
$ virtualenv venv

# activating the pipenv environment
$ cd venv/bin #windows environment you activate from Scripts folder

# if you have multiple python 3 versions installed then
$ source ./activate

Configured Enviromment

Environment variables

SECRET_KEY = #random string
DEBUG = #True or False
ALLOWED_HOSTS = #localhost
DATABASE_NAME = #database name (You can just use the default if you want to use SQLite)
DATABASE_USER = #database user for postgres
DATABASE_PASSWORD = #database password for postgres
DATABASE_HOST = #database host for postgres
DATABASE_PORT = #database port for postgres
ACCOUNT_EMAIL_VERIFICATION = #mandatory or optional
EMAIL_BACKEND = #email backend
EMAIL_HOST = #email host
EMAIL_HOST_PASSWORD = #email host password
EMAIL_USE_TLS = # if your email use tls
EMAIL_PORT = #email port

change all the environment variables in the .env.sample and don't forget to rename it to .env.

Run the project

After Setup the environment, you can run the project using the Makefile provided in the project folder.

help:
 @echo "Targets:"
 @echo "    make install" #install requirements
 @echo "    make makemigrations" #prepare migrations
 @echo "    make migrations" #migrate database
 @echo "    make createsuperuser" #create superuser
 @echo "    make run_server" #run the server
 @echo "    make lint" #lint the code using black
 @echo "    make test" #run the tests using Pytest

Preconfigured Packages

Includes preconfigured packages to kick start Django-Authentication by just setting appropriate configuration.

Package	Usage
django-allauth	Integrated set of Django applications addressing authentication, registration, account management as well as 3rd party (social) account authentication.
django-crispy-forms	django-crispy-forms provides you with a `crispy` filter and `{% crispy %}` tag that will let you control the rendering behavior of your Django forms in a very elegant and DRY way.

Contributing

Django-Authentication is a simple project, so you can contribute to it by just adding your code to the project to improve it.
If you have any questions, please feel free to open an issue or create a pull request.

Download Details:
Author: yezz123
Source Code: https://github.com/yezz123/Django-Authentication
License: MIT License

#django #python

iOS App Dev

1625133780

SingleStore: The One Stop Shop For Everything Data

SingleStore works toward helping businesses embrace digital innovation by operationalising “all data through one platform for all the moments that matter”

The pandemic has brought a period of transformation across businesses globally, pushing data and analytics to the forefront of decision making. Starting from enabling advanced data-driven operations to creating intelligent workflows, enterprise leaders have been looking to transform every part of their organisation.

SingleStore is one of the leading companies in the world, offering a unified database to facilitate fast analytics for organisations looking to embrace diverse data and accelerate their innovations. It provides an SQL platform to help companies aggregate, manage, and use the vast trove of data distributed across silos in multiple clouds and on-premise environments.

**Your expertise needed! **Fill up our quick Survey

#featured #data analytics #data warehouse augmentation #database #database management #fast analytics #memsql #modern database #modernising data platforms #one stop shop for data #singlestore #singlestore data analytics #singlestore database #singlestore one stop shop for data #singlestore unified database #sql #sql database

Rupert Beatty

1670568484