March 19th, 2007, 06:58 PM
|
|
Contributing User
|
|
Join Date: Jun 2005
Posts: 76
Time spent in forums: 8 h 58 m 16 sec
Reputation Power: 4
|
|
|
DB2 foreign keys
I have 3 tables. Table one (CERTDSCP) acts as the source for a primary key. Table two (Cert_Drafts) references those primary keys (a standard Primary key-foreign key relationship).
Now, with table three (Cert_Tests), I'm trying to reference the foreign key in table two (Cert_Drafts), and use it as a primary key in table three (Cert_Tests).
Is that permitted in DB2?
Code:
SQL0538N FOREIGN KEY "FKCERT_DRAFTS_MPS" does not conform to the description
of the parent key of table or nickname "TEAM.CERT_DRAFTS". SQLSTATE=42830
Code:
CREATE TABLE CERTDSCP
(
MPS_NUMBER VARCHAR(10) NOT NULL,
CONSTRAINT PK_MPS PRIMARY KEY (MPS_NUMBER),
);
CREATE TABLE Cert_Drafts
(
CertID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
MPS_NUMBER VARCHAR(10) NOT NULL,
CONSTRAINT PKCert_Drafts PRIMARY KEY (CertID),
CONSTRAINT FKCert_Numbers FOREIGN KEY (MPS_NUMBER) REFERENCES CERTDSCP
);
CREATE TABLE Cert_Tests
(
CertID BIGINT NOT NULL,
MPS_NUMBER VARCHAR(10) NOT NULL,
TestNumber INTEGER NOT NULL,
VersionNumber INTEGER NOT NULL,
CONSTRAINT PKCert_Tests PRIMARY KEY (CertID),
CONSTRAINT FKCert_Drafts_CID FOREIGN KEY (CertID) REFERENCES Cert_Drafts,
CONSTRAINT FKCert_Drafts_MPS FOREIGN KEY (MPS_NUMBER) REFERENCES Cert_Drafts,
CONSTRAINT UCert_Tests UNIQUE (MPS_NUMBER, TestNumber, VersionNumber)
);
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
