Simple answer do not mix with your other assumption. I hope you know primary key and foreign key relation for eg:
Primary key in a table is nothing but a column that is unique + not null where as foreign key is referring to that primary key table column.
If data inserting into foreign key table column that not matches to primary key it is nothing but duplicate record and it'll not insert with error throw.
In DB2 Load it'll not check duplicate/dependencies between two tables (primary key and foreign key table) bypass records and load data.
DB2 recommends checking dependency/duplicate record in terms of set integrity check after Load, if no duplicate/dependency found it'll take out your table from check integrity and if found you can create another table to put duplicate records into that and bring out from check integrity.
Query to check tables in check integrity after Load:
db2 "select 'set integrity for '||tabschema||'.'||tabname||' immediate checked ;'
from syscat.tables where tabschema='<schemaname for eg:HEDW' and not (status='N'
and access_mode='F')"
Command to bring out from check integrity:
db2 set integrity for HEDW .EMPLY_BON_TIME immediate checked
DB20000I The SQL command completed successfully.
I have modified ypercube's solution a little bit, so that both source and destination are not null, as is guaranteed by the original design. My CHECK
constraints are commented out, as they apparently will not work in MySQL. I kept them as comments because they document my intent, and they will work on other RDBMS.
CREATE TABLE PointType
( PointTypeID tinyint unsigned NOT NULL,
TypeDescription CHAR(20) NOT NULL,
PRIMARY KEY (PointTypeID),
UNIQUE (TypeDescription)
) ;
INSERT INTO PointType
(PointTypeID, TypeDescription)
VALUES
(1, 'Source'),
(2, 'Destination') ;
CREATE TABLE PointUsageQuota
( RouteID int unsigned NOT NULL,
PointTypeID tinyint unsigned NOT NULL,
PointID smallint(5) unsigned NOT NULL,
PRIMARY KEY (PointID), -- and this is what all the fuss is about
UNIQUE (RouteID , PointID, PointTypeID), -- target for the foreign keys
FOREIGN KEY (PointTypeID)
REFERENCES PointType (PointTypeID)
) ;
CREATE TABLE Route
( RouteID int unsigned NOT NULL,
SourcePointID smallint(5) unsigned NOT NULL,
SourceTypeID tinyint unsigned NOT NULL,
-- CHECK(SourceTypeID = 1),
FOREIGN KEY (RouteID , SourcePointID, SourceTypeID)
REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
DestinationPointID smallint(5) unsigned NOT NULL,
DestinationTypeID tinyint unsigned NOT NULL,
-- CHECK(DestinationTypeID = 2),
FOREIGN KEY (RouteID , DestinationPointID, DestinationTypeID)
REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
-- other fields
PRIMARY KEY (RouteID)
) ;
Testing:
insert into PointUsageQuota
values(1,1,666),(1,2,354);
INSERT INTO Route VALUES (1, 666, 1, 354, 2);
-- this fails:
INSERT INTO Route VALUES (2, 666, 1, 354, 2);
-- this fails too:
INSERT INTO Route VALUES (2, 354, 1, 666, 2);
Best Answer
You cannot use a subselect in a check constraint; you can only reference columns from the same table.
However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.
Presumably you have already defined a primary key constraint on
ACTOR.id
; if not, you need to define it, or a unique constraint on that column.