1

I try to create table GroupArticle in database CmsGroup, one column ArticleId references another table in another database? is this possible and correct syntax?

CmsArticle

CREATE DATABASE "CmsArticle"
WITH OWNER = db_admin
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
CONNECTION LIMIT = -1;

CREATE TABLE IF NOT EXISTS "Article"(
"ArticleId" SERIAL NOT NULL,
"PublishDate" timestamp without time zone,
PRIMARY KEY ("ArticleId")
);

CmsGroup

CREATE DATABASE "CmsGroup"
WITH OWNER = db_admin
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
CONNECTION LIMIT = -1;

CREATE TABLE IF NOT EXISTS "Group"(
"GroupId" SERIAL NOT NULL,
"PublishDate" timestamp without time zone,
PRIMARY KEY ("GroupId")
);

CREATE TABLE IF NOT EXISTS "GroupArticle"(
"GroupArticleId" SERIAL NOT NULL,
"GroupId" integer NOT NULL,
"ArticleId" integer NOT NULL,
FOREIGN KEY ("GroupId") REFERENCES "Group" ("GroupId") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("ArticleId") REFERENCES "CmsArticle.Article" ("ArticleId") ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY ("GroupArticleId")
);
  • 2
    If you need that, you probably want different schemas, not databases (and you should really avoid those dreaded quoted identifiers) – a_horse_with_no_name Nov 8 '15 at 12:21
2

NO. You cannot create foreign key references on a different database.

Create the tables on the same database, if there is no specific reason to have them in separate databases.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.