appserver/tests/v2-tests/appserv-tests/devtests/cmp/rosterext/sql/RosterApp_psql.sql

DROP TABLE TEAMPLAYER  cascade;
DROP TABLE PLAYER  cascade;
DROP TABLE TEAM  cascade;
DROP TABLE LEAGUE  cascade;

CREATE TABLE PLAYER
(
    PLAYER_ID VARCHAR(255) PRIMARY KEY,
    NAME      VARCHAR(255),
    POSITION  VARCHAR(255),
    SALARY DOUBLE PRECISION NOT NULL
);
#);

CREATE TABLE LEAGUE
(
    LEAGUE_ID VARCHAR(255) PRIMARY KEY,
    NAME      VARCHAR(255),
    SPORT     VARCHAR(255)
) ;
#);

CREATE TABLE TEAM
(
    TEAM_ID   VARCHAR(255) PRIMARY KEY,
    CITY      VARCHAR(255),
    NAME      VARCHAR(255),
    LEAGUE_ID VARCHAR(255),
    FOREIGN KEY (LEAGUE_ID)   REFERENCES LEAGUE (LEAGUE_ID)
);
#);

CREATE TABLE TEAMPLAYER
(
    PLAYER_ID VARCHAR(255),
        TEAM_ID   VARCHAR(255),
        CONSTRAINT PK_TEAMPLAYER PRIMARY KEY (PLAYER_ID, TEAM_ID),
    FOREIGN KEY (TEAM_ID)   REFERENCES TEAM (TEAM_ID),
    FOREIGN KEY (PLAYER_ID)   REFERENCES PLAYER (PLAYER_ID)
);
#);

commit;

quit;