--
-- $Id: pg_advisor_test.sql,v 1.5 2004/03/21 09:31:17 coelho Exp $
-- 

DROP SCHEMA bad_design CASCADE;

CREATE SCHEMA bad_design;

SET search_path TO bad_design;

-- some tables, ok.
CREATE TABLE t1(id INTEGER PRIMARY KEY, xxx INTEGER UNIQUE NOT NULL);
CREATE TABLE t2(id TEXT PRIMARY KEY, msg TEXT);
CREATE TABLE t3(id VARCHAR(64) PRIMARY KEY, msg TEXT);

-- no primary key
CREATE TABLE t4(id INTEGER UNIQUE NOT NULL, msg TEXT);
CREATE TABLE t5(id TEXT UNIQUE NOT NULL, msg TEXT);
CREATE TABLE t6(id VARCHAR(64) UNIQUE NOT NULL, msg TEXT);

-- nullable unique constraint
CREATE TABLE t7(id INTEGER PRIMARY KEY, msg TEXT UNIQUE NULL);

-- ok
CREATE TABLE f1(id INTEGER PRIMARY KEY, t1id INTEGER REFERENCES t1);

-- large primary keys with foreigns
CREATE TABLE f2(id INTEGER PRIMARY KEY, t2id TEXT REFERENCES t2);
CREATE TABLE f3(id INTEGER PRIMARY KEY, t3id VARCHAR(64) REFERENCES t3);

-- bad types
CREATE TABLE f4(id INTEGER PRIMARY KEY, t1id TEXT REFERENCES t1);
CREATE TABLE f5(id INTEGER PRIMARY KEY, t1id VARCHAR(64) REFERENCES t1);
CREATE TABLE f6(id INTEGER PRIMARY KEY, t2id VARCHAR(64) REFERENCES t2);

-- bad type sizes
CREATE TABLE f7(id INTEGER PRIMARY KEY, t3id VARCHAR(32) REFERENCES t3);

-- composite primary key (ok)
CREATE TABLE f8(id1 INTEGER, id2 INTEGER, PRIMARY KEY (id1, id2));

-- composite partial primary key
CREATE TABLE f9(id1 INTEGER, id2 INTEGER, PRIMARY KEY (id1, id2), msg TEXT);

-- composite foreign key
CREATE TABLE f10(id INTEGER PRIMARY KEY, id1 INTEGER, id2 INTEGER,
		FOREIGN KEY (id1, id2) REFERENCES f9);

-- foreign key not to primary key
CREATE TABLE f11(id INTEGER PRIMARY KEY, fxxx INTEGER REFERENCES t1(xxx));