Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jan 2011
    Posts
    117
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Post Postgresql Database [Help]

    Ok this is for postgresql, i didn't know where to put this but considering it uses sql i decided to put it in this section. If it's wrong, please let me know thank you.

    Anyway nice forum btw, greeting's to all!

    I was wondering if anyone could look through this code and tell me if there are any errors that need to be fixed. When i import it to postgresql or a mysql database it gives my errors.

    I was wondering if someone with much higher knowledge could rewrite this database or fix the remanining errors for me.

    I will be extremely thankful if you do this, plus my weeks of trying to solve this would be over, lol.

    But, here is the sql code:

    Code:
    CREATE TABLE site(
    	id       int4,
    	name     varchar(200),
    	PRIMARY KEY (id)
    );
    
    CREATE TABLE star(
    	id       int4,
    	name     varchar(200),
    	site_id  int4            REFERENCES site(id) ON DELETE CASCADE,
    	file_name varchar(200),
    	PRIMARY KEY (id, site_id)
    );
    
    CREATE TABLE room(
    	id       int4,
    	name     varchar(200),
    	site_id  int4            REFERENCES site(id) ON DELETE CASCADE,
    	color    int4,
    	back_color int4,
    	PRIMARY KEY (id, site_id)
    );
    
    CREATE TABLE master(
    	id       int4,
    	name     varchar(200),
    	site_id  int4            REFERENCES site(id) ON DELETE CASCADE,
    	color    int4,
    	back_color int4,
    	star_id  int4,
    	number   int4,
    	FOREIGN KEY (star_id, site_id) REFERENCES star(id, site_id),
    	PRIMARY KEY (id, site_id)
    );
    
    --CREATE TABLE guest(
    --	id       int4,
    --	name     varchar(200),
    --	site_id  int4            REFERENCES site(id) ON DELETE CASCADE,
    --	color    int4,
    --	status   int4,
    --	PRIMARY KEY (id, site_id)
    --);
    
    CREATE TABLE icon(
       id       int4,
       name     varchar(100),
       site_id  int4            REFERENCES site(id) ON DELETE CASCADE,
      PRIMARY KEY(id, site_id)
    );
    
    CREATE TABLE chatcast(
       id       int4,
       name     varchar(100),
       site_id  int4            REFERENCES site(id) ON DELETE CASCADE,
       color    int4,
       mess_color int4,
       to_whom  smallint,
       is_welcome bool,
       mess     text,
       PRIMARY KEY(id, site_id)
    );
    
    CREATE TABLE color(
       id       int4,
       color    int4,
       name     varchar(100),
       site_id  int4            REFERENCES site(id) ON DELETE CASCADE,
      PRIMARY KEY(id, site_id)
    );
    
    CREATE TABLE chatwatch(
    	id       int4,
    	name     varchar(200),
    	site_id  int4            REFERENCES site(id) ON DELETE CASCADE,
    	color    int4,
    	PRIMARY KEY (id, site_id)
    );
    
    -------MASTER LOG--------
    
    CREATE TABLE masterlog(
    	id       serial        not null,
      site_id   int4,
      recordtime  time        not null,
      recorddate  date        not null,
      master   varchar(200)   not null,
      info     text,
      FOREIGN KEY (site_id) REFERENCES site(id)
    );
    CREATE INDEX masterlog_siteid_idx ON masterlog (siteid);
    CREATE INDEX masterlog_master_idx ON masterlog (master);
    
    
    -------VS Section---------
    
    CREATE TABLE masterVSicon(
        icon_id   int4,
        master_id int4,
        site_id   int4          REFERENCES site(id) ON DELETE CASCADE,
        FOREIGN KEY (icon_id, site_id) REFERENCES icon(id, site_id)     ON DELETE CASCADE,
        FOREIGN KEY (master_id, site_id) REFERENCES master(id, site_id) ON DELETE CASCADE
    );
    
    --Altering tables and add new db structure
    ALTER TABLE site ADD 	display_name varchar(200);
    Well, i hope that is all you need to verify this, i believe there are some errors in that above code. I just do not know, i am a noob with this kinda thing. I have only played with mysql with vbulletins, and mybb software, never had to import databases other then to backup a database.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    How can we guess *which* errors it givesw you???

    Why not perform ONE operation at a time and not the error(s) each single one gives you?


    Why do you do the same thing two different ways??

    site_id int4 REFERENCES site(id) ON DELETE CASCADE,
    versus
    site_id int4,
    FOREIGN KEY (site_id) REFERENCES site(id)

    A couple of other things look suspicious, but I haven't used PostGres in maybe 10 years, so things could have changed since then.

    Do operations one at a time and record the errors.

    And don't try to go on to the next table until the one you are working on is successfully created.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Jan 2011
    Posts
    117
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Why thank you for the quick reply, you bring up a great point "run them at a time". I just get really impatient and want to get stuff done. This isn't my "own coding" of this sql file. Im trying to fix someone elses mistake, but thank you very much sir you have been great help


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •