Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

[SQL] Referential Integrity Details

Tags:
  1. Mar 30, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  2. jcsd
  3. Mar 31, 2015 #2

    Borg

    User Avatar
    Gold Member

    Yes, the foreign key is declared on the dependant 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:
    Code (SQL):

    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
    )
     
     
    Last edited by a moderator: Apr 9, 2015
  4. Mar 31, 2015 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, Borq
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: [SQL] Referential Integrity Details
  1. SQL basics (Replies: 4)

  2. SQL relationships (Replies: 3)

  3. Sql join? (Replies: 4)

  4. SQL Help (Replies: 2)

Loading...