The schema in this section is referred to throughout the design chapters of this guide. Let's assume you are designing a flight reservation system. At its simplest, the application requires database tables for the flights, the customers, and the reservations. Example 4.1, “DDL Example of a Reservation Schema” shows how the schema looks as defined in standard SQL DDL. For the VoltDB-specific details for creating tables, see CREATE TABLE. When defining the data types for table columns, refer to Table A.1, “Supported SQL Datatypes”.
Example 4.1. DDL Example of a Reservation Schema
CREATE TABLE Flight ( FlightID INTEGER UNIQUE NOT NULL, DepartTime TIMESTAMP NOT NULL, Origin VARCHAR(3) NOT NULL, Destination VARCHAR(3) NOT NULL, NumberOfSeats INTEGER NOT NULL, PRIMARY KEY(FlightID) ); CREATE TABLE Reservation ( ReserveID INTEGER NOT NULL, FlightID INTEGER NOT NULL, CustomerID INTEGER NOT NULL, Seat VARCHAR(5) DEFAULT NULL, Confirmed TINYINT DEFAULT '0' ); CREATE TABLE Customer ( CustomerID INTEGER UNIQUE NOT NULL, FirstName VARCHAR(15), LastName VARCHAR (15), PRIMARY KEY(CustomerID) );
To satisfy entity integrity you can specify a table's primary key by providing the usual PRIMARY KEY constraint on one or more of the table’s columns. To create a simple key, apply the PRIMARY KEY constraint to one of the table's existing columns whose values are unique and not null, as shown in Example 4.1, “DDL Example of a Reservation Schema”.
To create a composite primary key from a combination of columns in a table, apply the PRIMARY KEY constraint to multiple columns with typical DDL such as the following:
$ sqlcmd 1> CREATE TABLE Customer ( 2> FirstName VARCHAR(15), 3> LastName VARCHAR (15), 4> CONSTRAINT pkey PRIMARY KEY (FirstName, LastName) 5> );