Creating a column that only accepts entry when other table's value equals to true

Question

I am creating a small database and need to create a column in table 1 that will only accept an entry when table 2 value equals to true.

I came up with this code:

ALTER TABLE MarshallGroup
ADD TRANumber NUMERIC(4, 0) CHECK (Member.isMarshall = 'True')

However, I get this error:

The multi-part identifier "Member.isMarshall" could not be bound.

I am aware that I have to use inner join but I don't know where to use it (like I said I am new in SQL).

EDIT: Here are the tables

**First table: Member**     
(PK)TRANumber
firstName
lastName 
gender 
ClubID 
shortNameCatID 
age 
year 
isMarshall <-- this one has to equal to true to be accepted in table 2


**Second Table: MarshallGroup**
(PK)marshallGroupID
(soon to be FK)TRANumber <- this is the column Im creating 
groupNumber

You cannot add a check constraint that refers to a different table - you can only check for literal values, or for values in the current table. If you really needs this, you have to handle that validation check in a trigger in SQL Server, or in your application logic — marc_s, Dec 30 '18 at 09:37
There is a way but it is messy IMO: https://stackoverflow.com/a/2588427/575376 — juergen d, Dec 30 '18 at 09:39
@juergend First table: Member (PK)TRANumber firstName lastName gender ClubID shortNameCatID age year isMarshall <-- this one has to equal to true to be accepted in table 2 Second Table: MarshallGroup (PK)marshallGroupID (soon to be FK)TRANumber <- this is the column Im creating groupNumber — Gonper, Dec 30 '18 at 09:40
Bloody hell. Sorry for the messy comment. I tried to space it out but it just deletes stuff — Gonper, Dec 30 '18 at 09:44
@juergend If I want to limit entries for a specific column, do I have to do it while creating it or can I sort it out after it was created? — Gonper, Dec 30 '18 at 09:46
You can do it later: `alter table MarshallGroup add constraint const_name check (...)` — juergen d, Dec 30 '18 at 09:48
Personally I would handle that check in the program logic and not in SQL. — juergen d, Dec 30 '18 at 10:04
@Michael done, found the answer, which is why I deleted it, didnt wanted to bother anyone else. Sorry about that. Looking forward to your answer! — Gonper, Nov 04 '19 at 21:24
@Gonper No worries. you can always answer your own question too to share your wisdom :) — Michael, Nov 04 '19 at 21:51

score 0 · Answer 1 · answered Dec 30 '18 at 12:30

If I understand correctly, you can do what you want with a foreign key constraint and computed column:

create table Members as (
    . . .,
    TRANumber number(4, 0),
    isMarshall varchar(10),
    . . .
    constraint chk_members_isMarshall check (isMarshall in ('True', 'False')),
    constraint unq_members_TRANumber_isMarshall unique (TRANumber, isMarshall)
);

create table MarshallGroups as (
    . . .,
    isMarshall as ('True') persisted,
    constraint fk_MarshallGroups_Members
        foreign key (TRANumber, isMarshall) references members(TRANumber, isMarshall)
);

The alternatives to this method are either to use a trigger or a user-defined function.

Creating a column that only accepts entry when other table's value equals to true

EDIT: Here are the tables

1 Answers1