• Home
• us.fatehi
• schemacrawler-testdb
• 16.24.2
• source code
• 30_tables_01_base_A.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

db.books.30_tables_01_base_A.sql Maven / Gradle / Ivy

-- Table with default for a column, and a check constraint
CREATE TABLE Authors
(
  Id INTEGER NOT NULL,
  FirstName VARCHAR(20) NOT NULL,
  LastName VARCHAR(20) NOT NULL,
  Address1 VARCHAR(255),
  Address2 VARCHAR(255),
  City VARCHAR(50),
  State CHAR(2),
  PostalCode VARCHAR(10),
  Country VARCHAR(50) DEFAULT 'USA',
  CONSTRAINT PK_Authors PRIMARY KEY (Id),
  CONSTRAINT CHECK_UPPERCASE_State CHECK (State=UPPER(State))
)
;

-- Table with unique constraint, and self-referencing foreign key
CREATE TABLE Books
(
  Id INTEGER NOT NULL,
  Title VARCHAR(255) NOT NULL,
  Description VARCHAR(255),
  PublisherId INTEGER NOT NULL,
  PublicationDate DATE,
  Price FLOAT,
  PreviousEditionId INTEGER,
  CONSTRAINT PK_Books PRIMARY KEY (Id),
  CONSTRAINT FK_PreviousEdition FOREIGN KEY (PreviousEditionId) REFERENCES Books (Id),
  CONSTRAINT U_PreviousEdition UNIQUE (PreviousEditionId)
)
;

-- Contains unnamed foreign key
-- Foreign keys have a different natural and alphabetical sort order
CREATE TABLE BookAuthors
(
  BookId INTEGER NOT NULL,
  AuthorId INTEGER NOT NULL,
  SomeData VARCHAR(30),
  FOREIGN KEY (BookId) REFERENCES Books (Id),
  CONSTRAINT Z_FK_Author FOREIGN KEY (AuthorId) REFERENCES Authors (Id)
)
;