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. Finally, specify the table constraints including primary key, foreign key, and check constraints.The column constraints include not null, unique, primary key, check, foreign key constraints. For example, the not-null constraint enforces the values in the column cannot be NULL. The column constraints specify rules that data stored in the column must follow. Each column consists of the column name, the kind of data that column stores, the length of data, and the column constraint. Third, specify a comma-separated list of table columns.When you use the IF NOT EXISTS option and the table already exists, PostgreSQL issues a notice instead of the error and skips creating the new table. The IF NOT EXISTS option allows you to create the new table only if it does not exist. Second, creating a table that already exists will result in a error.First, specify the name of the table after the CREATE TABLE keywords.) Code language: SQL (Structured Query Language) ( sql ) The following illustrates the basic syntax of the CREATE TABLE statement: CREATE TABLE table_name (Ĭolumn1 datatype( length) column_contraint,Ĭolumn2 datatype( length) column_contraint,Ĭolumn3 datatype( length) column_contraint, To create a new table, you use the CREATE TABLE statement. Tables allow you to store structured data like customers, products, employees, etc. PostgreSQL CREATE TABLE syntaxĪ relational database consists of multiple related tables. Summary: in this tutorial, you will learn how to use the PostgreSQL CREATE TABLE statement to create new a new table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |