![]() ![]() (SQLite does not support this usage.) 1.7.4. ALTER TABLE can also be used to remove (drop) constraints from a table. Some database systems also allow the addition of a primary key to an existing table using the ALTER TABLE command, as long as the existing data would conform to the constraint. ![]() If the primary key is a single column, then you may simply put the phrase PRIMARY KEY at the end of the column definition:ĭROP TABLE IF EXISTS test2 DROP TABLE IF EXISTS test3 CREATE TABLE test2 ( x VARCHAR ( 10 ), PRIMARY KEY ( x ) ) CREATE TABLE test3 ( x INTEGER, y INTEGER, PRIMARY KEY ( x, y ) ) It is easiest to create a primary key when creating the table. There are a few ways to create a primary key on a table. That is, you may not insert the pair (4, 10) twice, but you may insert all of (4, 10), (4, 11), and (5, 10) (assuming none of those pairs is already in the table). Instead, it is the pair of values which must be unique. This does not mean that each column is a primary key, e.g., that each column is independently unique. For this table, the primary key is the pair ( author_id, award_id). For other primary key column types, or multi-column primary keys, NULL values are permitted in SQLite.)Īn example of a multi-column primary key can be found in the table authors_awards. If you insert a NULL into an integer primary key column, SQLite will generate a unique value for you. (SQLite varies from this standard behavior, though. UPDATE authors SET author_id = 1 WHERE author_id = 2 īecause we need to compare primary key values to ensure uniqueness, it is also forbidden to insert NULL values into a primary key column. Either of the following queries will result in an error because there already exists a row in the database with author_id = 1: Deleting rows can never violate a primary key constraint, but inserts and updates can. The database will enforce the uniqueness property of the author_id column by preventing any data modification queries which would result in a duplicate primary key. The table authors, for example, has author_id as a primary key. Some examples can be found in our books database. The id columns discussed in Chapter 1.4 - special columns used specifically to ensure each row can be uniquely identified - are commonly used as primary keys. It also prevents unnecessary duplication of data that could result in incorrect statistics (for example, when taking counts or sums). Controlling for uniqueness ensures that we can associate a specific, single item of data with data in another table by storing the key value in the other table. Primary keys ¶Ī primary key is a column or set of columns of a table which are required to contain unique values for each row stored in the table. (Domain constraints are not enforced in SQLite, so we cannot demonstrate using the book’s database.) Below we discuss constraints that enforce data properties for entire tables - primary and foreign key constraints - and those that apply to individual rows: not null, uniqueness, and check constraints. ![]() ![]() One of the simplest types of constraints are domain constraints: data entered into a column is constrained to be of the type defined for the column. This book does not cover triggers.) We focus in this chapter on database constraints.Ĭonstraints are properties of the database that restrict data in various ways. (Triggers are actions the database can perform when certain events, such as an update to a row, occur in some databases, the actions can even be complete small programs using implementation-specific programming languages. This can be achieved in many ways: application software can do checks to ensure data is entered and maintained correctly, separate programs can test and report on data consistency, and databases can enforce correctness using constraints and triggers. Constraints ¶Īs data is critical to so many organizations and applications, it is worth doing everything possible to ensure the correctness and consistency of the data in databases. Other examples will use tables created just for the example, then discarded. Some examples in this chapter will use the books table and related tables (see Appendix A for a full description of these tables). In this chapter, we explore the different ways in which data may be constrained in order to help preserve the correctness of our data. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |