/* filename: HistoryPlaces.sql */

/* Connect to the database */
\c hp
   

    /* Remove the table in case it exists */

    DROP TABLE Photo CASCADE;
	DROP TABLE Place CASCADE;
	
	/* Create Tables */

	CREATE TABLE Place (

		pid			serial NOT NULL,
		parent      integer NOT  NULL,
		pname		varchar(100) DEFAULT NULL,
		comment		text DEFAULT NULL,
		photoid     integer DEFAULT NULL, 

		PRIMARY KEY(pid)
		);

    /* Create the table */

    CREATE TABLE Photo(

        photoid         serial not null,  
        name          	varchar(35) DEFAULT NULL,
        url             text DEFAULT NULL,
        comment         text DEFAULT NULL,
        date        	date DEFAULT NULL,
        photographer	varchar(35) DEFAULT NULL,
        placeid		    integer NOT NULL,    
		
        PRIMARY KEY (photoid)
		);
    
    ALTER TABLE Photo ADD FOREIGN KEY (placeid) REFERENCES Place(pid) ON DELETE SET DEFAULT;