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:<\/p>\n
PRIMARY KEY:<\/strong> 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.<\/p>\n NOT NULL:<\/strong> 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 \u2018name\u2019 column must have a certain value, NOT NULL constraint is applied to it.<\/p>\n FOREIGN KEY:<\/strong> This constraint is used to enforce the \u2018exists\u2019 relationship between two tables. (Ensure that the SQL statement ends with a semi-colon. You may span one statement over multiple lines in the console) Finally, we create another \u2018Invoices\u2019 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 \u2018Products\u2019 table and CustlD in the \u2018Customers\u2019 table. The \u2018Invoices\u2019 table also contains the \u2018price\u2019 column.<\/p>\n To confirm that our tables have been successfully created, use the .tables command:<\/p>\n sqlite> .tables SQLite stores the schema of all databases in the SQL1TEJVIASTER table. We can fetch names of our databases and tables with the following command:<\/p>\n To terminate the current session of SQLiteS activity use .quit command.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 …<\/p>\n
\nLet us create a Products table in \u2018mydatabase\u2019 that we created above. As shown in Figure 7.1, diagram, the \u2018products\u2019 table consists of ProductID, Name, and Price columns, with ProductlD as its primary key.<\/p>\nsqlite> CREATE TABLE Products (\r\n. . . > ProductID INTEGER PRIMARY KEY\r\nAUTOINCREMENT, \r\n...> Name TEXT (20),\r\n...> Price INTEGER\r\n. . . > ) ;<\/pre>\n
\nWe also create another \u2018Customers\u2019 table in the same database with CustlD and Name fields. The CustlD field should be defined as the primary key.<\/p>\nsqlite> CREATE TABLE Customers (\r\n. . . > CustlD INTEGER PRIMARY KEY\r\nAUTOINCREMENT, \r\n. . . > Name TEXT (20) ,\r\n. . . > GSTIN TEXT (15)\r\n. . . . > ) ;<\/pre>\n
sqlite> CREATE TABLE Invoices ( \r\n. . . > InvID INTEGER PRIMARY KEY\r\nAUTOINCREMENT, \r\n. . . > CustlD INTEGER REFERENCES\r\nCustomers (CustlD), \r\n. . . > ProductID INTEGER REFERENCES\r\nProducts (ProductID), \r\n. . . > Quantity INTEGER (5)\r\n. . . > ) ;<\/pre>\n
\nCustomers Invoices Products<\/p>\nsqlite> SELECT * FROM s'qlite_master WHERE \r\ntype='table';<\/pre>\n