![]() If you most often retrieve records, you must look further to define the criteria for retrieving records and create indexes to improve the performance of these retrievals. It may be more efficient to drop all indexes before modifying a large number of records, and re-create the indexes after the modifications. This is because the driver must maintain the indexes as well as the database tables, thus slowing down the performance of record inserts, updates, and deletes. If you most often insert, update, and delete records, then the fewer indexes associated with the table, the better the performance. The two most common operations on a table are to: ![]() Apply the primary key, foreign key, not null, unique, and check constraints to columns of a table.Before you create indexes for a database table, consider how you will use the table.Use the IF NOT EXISTS option to create the new table only if it does not exist.Use the CREATE TABLE statement to create a new table.The following shows the relationship between the accounts, roles, and account_roles tables: Summary REFERENCES roles (role_id) Code language: SQL (Structured Query Language) ( sql ) The role_idcolumn references the role_idcolumn in the roles table, we also need to define a foreign key constraint for the role_idcolumn. REFERENCES accounts (user_id) Code language: SQL (Structured Query Language) ( sql ) PRIMARY KEY (user_id, role_id) Code language: SQL (Structured Query Language) ( sql )īecause the user_idcolumn references to the user_idcolumn in the accounts table, we need to define a foreign key constraint for the user_idcolumn: FOREIGN KEY (user_id) The primary key of the account_roles table consists of two columns: user_id and role_id, therefore, we have to define the primary key constraint as a table constraint. The following statement creates the account_roles table that has three columns: user_id, role_id and grant_date. The following statement creates the roles table that consists of two columns: role_id and role_name: CREATE TABLE roles( The following statement creates the accounts table: CREATE TABLE accounts ( We will create a new table called accounts that has the following columns: Table constraints are similar to column constraints except that they are applied to more than one column. Unlike the primary key, a table can have many foreign keys. FOREIGN KEY – ensures values in a column or a group of columns from a table exists in a column or group of columns in another table.CHECK – a CHECK constraint ensures the data must satisfy a boolean expression.The primary key constraint allows you to define the primary key of a table. A table can have one and only one primary key. PRIMARY KEY – a primary key column uniquely identify rows in a table.UNIQUE – ensures the values in a column unique across the rows within the same table.NOT NULL – ensures that values in a column cannot be NULL. ![]() PostgreSQL includes the following column constraints: Note that some table constraints can be defined as column constraints like primary key, foreign key, check, unique constraints.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |