How To Insert Or Update Without Primary Key Sqlite

SQLite is a database management system, which is used to manage the data of the relational database simply like MySQL, it likewise contains a lot of constraints such as UNIQUE, PRIMARY Fundamental, and FOREIGN Primal like other databases.

And then what are constraints and how do they work in SQLite? This write-up is a comprehensive guide about constraints and on how to use the PRIMARY key constraint in SQLite.

What are the constraints in SQLite

Constraints are the principles, co-ordinate to which information of the same information type is inserted into the table, it organizes the structure of the column and as well ensures the reliability of the information which is stored in the table. There are many constraints, some of them are:

Primary key
Strange key
Unique constraint
Default constraint
Check constraint

What is the Primary Key in SQLite

A main fundamental is the unique column of the table, which ensures the entries inserted in the cavalcade should exist unique and is also used to refer to the foreign key of other tables. The table tin be created without using the primary key, but if the table is using the principal key, then only i primary cardinal tin can exist assigned to the table. Master keys are very essential when creating a database that has a variety of tables, and to create relationships among the different databases, the chief key is used.

A master key must follow these rules:

The column of the primary fundamental cannot exist divers with the NULL value
The data inserted in the rows of the cavalcade of the Primary key should be unique
The primary key should exist referred to as the particular strange key of another tabular array
The tabular array can contain merely i primary key

A chief central cannot exist a NULL value in the databases, but in the example of SQLite, information technology can be assigned a Nada value, because of the "long-standing coding oversight".

A tabular array should have only one primary key, but multiple columns can exist defined in a single chief key, when multiple columns are being used as a single principal fundamental, then it is called a composite principal fundamental.

How many ways to add a primary key to whatsoever tabular array in SQLite

There are 2 ways to assign a primary key while creating a tabular array, which are:

To the single column of a tabular array
To the multiple columns of a table

How to create a table assigning a primary key to i cavalcade in SQLite

Nosotros can create a table by assigning a main cardinal to a unmarried column, its full general syntax will be:

CREATE TABLE TABLE_NAME (column_name1 <datatype> Chief KEY Not Aught , column_name2 <datatype> );

The explanation of this syntax is as:

The clause of CREATE Tabular array is used to create a table
Type the table name instead of table_name
Blazon the column proper name instead of column_name1, and too write its information type
Employ a clause of Main Primal, if you are assigning the column as a principal key, and also define it as either NULL or Non Naught
Blazon the proper name 2nd column replacing column_name2

To sympathise it, consider an instance: nosotros create a table of schoolhouse students, having the unique student ids, named, (std_id), and student names, named, (std_name). In this table, students names can be the same but students ids cannot be the same, and then we assign the primary key to std_id as:

CREATE TABLE school_students (std_id INTEGER PRIMARY KEY NOT Nothing , std_names);

The tabular array of school_students has been created, having a single column as a primary key.

How to create a table assigning a primary key to multiple columns in SQLite

We can create a table by assigning a primary key to multiple columns, its full general syntax volition be:

CREATE TABLE TABLE_NAME (column_name1 <datatype> , column_name2 <datatype>, column_name3 <datatype>, Principal KEY (column_name1, column_name2) );

In the higher up syntax, nosotros defined the primary cardinal at the end of the statement, with the names of the cavalcade in parentheses that are to be included in the primary fundamental.

Again to empathise this, we consider an example of a table employees_data, nosotros will create it with three columns which are emp_id, emp_name, and emp_dep, and afterward this we assign emp_id and emp_name as a Master Primal:

CREATE Tabular array employees_data (emp_id INTEGER , emp_name TEXT, emp_dep TEXT,emp_name PRIMARY Cardinal (emp_id, emp_email) );

The table has been created with the primary key having 2 columns in it.

How to add a primary cardinal to the existing table in SQLite

We cannot add together a primary cardinal to the existing tabular array in SQLite using Alter clause, but to assign a main key to a table in SQLite, we will follow the steps:

The foreign key constraint should be checked off
Rename the table to some other name
Create a new table that has the same construction, which was created before
Copy the data from that table to this table
Delete the renamed tabular array
In the cease, turn on the foreign key constraints

Nosotros have a table in a database, named, students_data, which has no principal key and its contents tin can be displayed using:

SELECT * FROM students_data;

To assign the "id" a primary key, we will run the following commands:

PRAGMA foreign_keys=off;
Begin TRANSACTION;
ALTER Table students_data RENAME TO new_students_data;
CREATE TABLE students_data (id INTEGER Not Aught PRIMARY KEY , proper noun TEXT NOT NULL , omnipresence INTEGER Not NULL );
INSERT INTO students_dataSELECT * FROM new_students_data;
Drib TABLE new_students_data;
COMMIT;
PRAGMA foreign_keys= ON;

To check whether the primary cardinal is assigned to the column named, id, run the control:

PRAGMA table_info( [students_data] );

The master primal has successfully been assigned to the table students_data.

How to delete the main cardinal constraint in SQLite

Similar other databases, we cannot drop the constraint by using the Driblet and Change commands, to delete the PRIMARY Central constraints we should follow the aforementioned procedure nosotros opt for calculation the constraint to an existing table and redefine the structure of the table without defining a primary primal to whatever column. Allow united states consider the to a higher place example again of adding a primary fundamental, and nosotros delete the primary fundamental every bit:

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
Modify Table students_data RENAME TO new_students_data;
CREATE TABLE students_data (id INTEGER NOT Nothing , name TEXT Not NULL , attendance INTEGER Not NULL );
INSERT INTO students_dataSELECT * FROM new_students_data;
Driblet TABLE new_students_data;
COMMIT;
PRAGMA foreign_keys= ON;

Conclusion

A chief key is very useful especially to found relationships of a table with others, as a foreign key always refers to the principal key of the tabular array, moreover, a table has but ane primary central, but its fields can be one or more than than ane. In this article, we have discussed how the main key is used in SQLite and also discussed with examples how a primary fundamental is assigned to one or more columns also as to the table which is already existing without a chief key.

Near the author

I'one thousand an Engineering graduate and my passion for IT has brought me to Linux. Now here I'thousand learning and sharing my knowledge with the world.

Source: https://linuxhint.com/sqlite-primary-key/

Posted by: robinsonhans1996.blogspot.com