Postgresql Database [Help]

[xxcorrosionxx]

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:

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.

[Old Pedant]

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.

[xxcorrosionxx]

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 :rolleyes: