Select subquery CHECK constraint DB2

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}

up vote
1
down vote

favorite

I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.

db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"

I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.

How can I fix this?

asked 5 hours ago

user2251234

New contributor

add a comment |

up vote
1
down vote

favorite

I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.

db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"

I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.

How can I fix this?

asked 5 hours ago

user2251234

New contributor

add a comment |

up vote
1
down vote

favorite

I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.

db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"

I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.

How can I fix this?

asked 5 hours ago

user2251234

New contributor

I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.

db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"

I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.

How can I fix this?

db2 constraint

asked 5 hours ago

user2251234

New contributor

asked 5 hours ago

user2251234

New contributor

asked 5 hours ago

user2251234

New contributor

asked 5 hours ago

user2251234

asked 5 hours ago

user2251234

New contributor

user2251234 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment |

1 Answer
1

active

oldest

votes

up vote
3
down vote

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.

ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)

Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it or at least create a unique index on that column.

edited 4 hours ago

answered 5 hours ago

mustaccio

8,82262135

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

user2251234 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223340%2fselect-subquery-check-constraint-db2%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
3
down vote

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.

ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)

Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it or at least create a unique index on that column.

edited 4 hours ago

answered 5 hours ago

mustaccio

8,82262135

add a comment |

up vote
3
down vote

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.

ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)

Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it or at least create a unique index on that column.

edited 4 hours ago

answered 5 hours ago

mustaccio

8,82262135

add a comment |

up vote
3
down vote

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.

ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)

Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it or at least create a unique index on that column.

edited 4 hours ago

answered 5 hours ago

mustaccio

8,82262135

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.

ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)

Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it or at least create a unique index on that column.

edited 4 hours ago

answered 5 hours ago

mustaccio

8,82262135

edited 4 hours ago

answered 5 hours ago

mustaccio

8,82262135

answered 5 hours ago

mustaccio

8,82262135

answered 5 hours ago

mustaccio

8,82262135

add a comment |

user2251234 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

user2251234 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vrftsjtryk