1 / 16

CS422 Principles of Database Systems Schema Definitions and Constraints

Learn about schema and constraint definitions in databases, covering data types, constraints, checks, and schema management in PostgreSQL.

vfalcon
Download Presentation

CS422 Principles of Database Systems Schema Definitions and Constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS422 Principles of Database SystemsSchema Definitions and Constraints Chengyu Sun California State University, Los Angeles

  2. Create Table Schema CREATE TABLE table ( name type [DEFAULT value] [column_constraints], ... [table_constraints] );

  3. Data Types • char(n), varchar(n), text • int, smallint, bigint • real, float • numeric(n,m), decimal(n,m) • serial • boolean • date, time, timestamp

  4. Column constraints unique primary key references not null Table constraints unique primary key foreign key ... references Constraints

  5. Checks create table test1 ( a1 int check ( a1 > 10 ), a2 int, check (a2 > a1) ); • Attribute-based check (column constraint) • Checks that involve only one column • Tuple-based check (table constraint) • Checks that involve multiple columns

  6. Another Check Example • Simulate foreign key constraint with CHECK • Just doesn’t work as well CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) CHECK ( beer IN (SELECT name FROM Beers)), price REAL CHECK ( price <= 5.00 ) );

  7. Assertions • Checks that involve multiple tables CREATE ASSERTION <name> CHECK ( <condition> );

  8. Assertion Examples CREATE ASSERTION FewBar CHECK ( (SELECT COUNT(*) FROM Bars) <= (SELECT COUNT(*) FROM Drinkers) ); CREATE ASSERTION Fk CHECK ( NOT EXIST ( (select beer from sells) except (select beer from beers) ) );

  9. PostgreSQL Limitations • No subqueries in CHECK • No ASSERTION

  10. Constraint Name create table test2 ( a1 int constrainttest1_pk primary key, a2 int constrainttest1_k1 unique, a3 int, a4 int, constrainttest1_k2 check (a3 > a4) );

  11. Change or Drop Table • ALTER TABLE • add/remove column • add/remove constraints • change table/column name • ... • DROP TABLE

  12. Create Database Schema CREATE SCHEMA schema; SQL PostgreSQL Clusters Databases Catalogs Schemas Schemas Schema Elements Schema Elements

  13. Why Use Schema? • Name space • companyA.test1 is different from cysun.test1 • Organize related tables together • put all the “test” tables under a test schema • Manage access privileges

  14. PostgreSQL Schema-related Commands ... • Create schema • create schemaschema_name; • List all schemas in current database • \dn • Create tables in a schema • createschema_name.table_name • List all tables in a schema • \dtschema_name.*

  15. ... PostgreSQL Schema-related Commands • Show current search path • show search_path; • Change search path • set search_path to schema1,schema2; • Drop a schema • dropschema_name [cascade];

  16. Special Schemas • public • information_schema

More Related