SQL

DDL – Creating a Schema

• A schema is a place that holds a set of tables needed for a particular system
• First remove the schema if it already exists

/* DDL – Creating a Schema */
	DROP SCHEMA IF EXISTS ucfscde cascade;

(NB – the cascade is important – if the schema has data it won’t be deleted without cascade)
• So if you are unsure, rename it instead

ALTER SCHEMA ucfscde rename to ucfscde_backup_23Nov2018;

• Now create the new schema

CREATE SCHEMA ucfscde;

Creating a Table

	CREATE TABLE <TABLENAME>
	(FIELD_NAME_1 <FIELD_TYPE> NOT NULL,
	FIELD_NAME_2 <FIELD_TYPE>,
	FIELD_NAME_3 <FIELD_TYPE>);

• Deleting a Table
• Modifying a Table – Adding a Column
• Modifying a Table – Removing a Column

Creating Integrity Constraints

Primary Keys

ALTER TABLE ucbqzx9.bus_station ADD CONSTRAINT bus_station_pk PRIMARY KEY (bus_station_id);

Foreign Keys

ALTER TABLE ucbqzx9.waiting_room ADD CONSTRAINT waiting_room_fk FOREIGN KEY(bus_station_id) REFERENCES ucbqzx9.bus_station (bus_station_id);

Domain Constraints

ALTER TABLE ucbqzx9.bus_station_cleaner ADD CONSTRAINT bus_station_cleaner_check CHECK (hours_per_week<49);

Unique Constraints:

如果使用ID作为主键，必须确保真正的主键是唯一的，您创建的每个表都要有一个唯一的约束

ALTER TABLE ucbqzx9.bus_station_cleaner ADD CONSTRAINT bus_station_cleaner_unique UNIQUE (bus_station_id, cleaner_id);

DDL – Creating a Schema
• A schema is a place that holds a set of tables needed for a particular system
• First remove the schema if it already exists
DROP SCHEMA IF EXISTS ucfscde cascade;
(NB – the cascade is important – if the schema has data it won’t be deleted without cascade)
• So if you are unsure, rename it instead
ALTER SCHEMA ucfscde rename to ucfscde_backup_23Nov2018;
• Now create the new schema
CREATE SCHEMA ucfscde;
• Creating a Table
CREATE TABLE
(FIELD_NAME_1 <FIELD_TYPE> NOT NULL,
FIELD_NAME_2 <FIELD_TYPE>,
FIELD_NAME_3 <FIELD_TYPE>);
• Deleting a Table
• Modifying a Table – Adding a Column
• Modifying a Table – Removing a Column

• DDL - Creating Integrity Constraints
Primary Keys
ALTER TABLE ucbqzx9.bus_station ADD CONSTRAINT bus_station_pk PRIMARY KEY (bus_station_id);
Foreign Keys
ALTER TABLE ucbqzx9.waiting_room ADD CONSTRAINT waiting_room_fk FOREIGN KEY(bus_station_id) REFERENCES ucbqzx9.bus_station (bus_station_id);
Domain Constraints
ALTER TABLE ucbqzx9.bus_station_cleaner ADD CONSTRAINT bus_station_cleaner_check CHECK (hours_per_week<49);
Unique Constraints: 如果使用ID作为主键，必须确保真正的主键是唯一的，您创建的每个表都要有一个唯一的约束
ALTER TABLE ucbqzx9.bus_station_cleaner ADD CONSTRAINT bus_station_cleaner_unique UNIQUE (bus_station_id, cleaner_id);