[SQL] Referential Integrity Details

[WWGD]

Hi a probably dumb question on Referential Integrity:

I think I understand the basic idea/motivation for it, but one thing I am confused about is this: just when/how
do we declare a column in a given table T' to be a foreign key related to the primary key for table T? Is it when we design/create table T' ? I know how, in ACCESS BQE to declare a field to be a primary key, but I have not seen how to make/declare a field in another table to be a foreign key associated to the primary key (though, strangely, once these two keys have been declared, I do know how to enforce referential integrity). And how is this done in SQL?
Thanks in Advance.

 

[Borg]

Yes, the foreign key is declared on the dependent table. For example, if you have a USERS table with a primary key of USER_ID and you wanted to create a USER_LOGIN table that tracked login attempts by user, you might create a table like this:

CREATE TABLE USER_LOGIN (
    USER_LOGIN_ID NUMERIC(14,0) IDENTITY NOT NULL PRIMARY KEY,
    USER_ID NUMERIC(14) NOT NULL REFERENCES USERS(USER_ID),
    SUCCESS TINYINT NOT NULL,
    DATETIME_ATTEMPTED VARCHAR(14) NOT NULL,
    ATTEMPTED_BY_USER_IP VARCHAR(25) NULL
)

 

[WWGD]

Thanks, Borq