Python Data Persistence – Constraints

by

Python Data Persistence – Constraints

Constraints enforce restrictions on data that a column can contain. They help in maintaining the integrity and reliability of data in the table. Following clauses are used in the definition of one or more columns of a table to enforce constraints:

PRIMARY KEY: Only one column in a table can be defined to be a primary key. The value of this table will uniquely identify each row (a record) in the table. The primary key can be set to AUTOINCREMENT if its type is INTEGER. In that case, its value need not be manually filled.

NOT NULL: By default value for any column in a row can be left null. NOT NULL constraint ensures that while filling a new row in the table or updating an existing row, the contents of specified columns are not allowed to be null. In the above definition, to ensure that the ‘name’ column must have a certain value, NOT NULL constraint is applied to it.

FOREIGN KEY: This constraint is used to enforce the ‘exists’ relationship between two tables.
Let us create a Products table in ‘mydatabase’ that we created above. As shown in Figure 7.1, diagram, the ‘products’ table consists of ProductID, Name, and Price columns, with ProductlD as its primary key.

sqlite> CREATE TABLE Products (
. . . > ProductID INTEGER PRIMARY KEY
AUTOINCREMENT, 
...> Name TEXT (20),
...> Price INTEGER
. . . > ) ;

(Ensure that the SQL statement ends with a semi-colon. You may span one statement over multiple lines in the console)
We also create another ‘Customers’ table in the same database with CustlD and Name fields. The CustlD field should be defined as the primary key.

sqlite> CREATE TABLE Customers (
. . . > CustlD INTEGER PRIMARY KEY
AUTOINCREMENT, 
. . . > Name TEXT (20) ,
. . . > GSTIN TEXT (15)
. . . . > ) ;

Finally, we create another ‘Invoices’ table. As shown in the figure 7.1 diagram, this table has InvID as the primary key and two foreign key columns referring to ProductID in ‘Products’ table and CustlD in the ‘Customers’ table. The ‘Invoices’ table also contains the ‘price’ column.

sqlite> CREATE TABLE Invoices ( 
. . . > InvID INTEGER PRIMARY KEY
AUTOINCREMENT, 
. . . > CustlD INTEGER REFERENCES
Customers (CustlD), 
. . . > ProductID INTEGER REFERENCES
Products (ProductID), 
. . . > Quantity INTEGER (5)
. . . > ) ;

To confirm that our tables have been successfully created, use the .tables command:

sqlite> .tables
Customers Invoices Products

SQLite stores the schema of all databases in the SQL1TEJVIASTER table. We can fetch names of our databases and tables with the following command:

sqlite> SELECT * FROM s'qlite_master WHERE 
type='table';

To terminate the current session of SQLiteS activity use .quit command.