Professional Web Applications Themes

SQL Newbie Primay-Foreign Key question - Microsoft SQL / MS SQL Server

I have 2 tables CREATE TABLE [dbo].[Accounts] ( [Acct_GUID] [uniqueidentifier] NOT NULL , [Login] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Pwd] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Acct_Owner_GUID] [uniqueidentifier] NULL , ) ON and CREATE TABLE [dbo].[Customers] ( [Cust_GUID] uniqueidentifier ROWGUIDCOL NOT NULL , [Acct_GUID] [uniqueidentifier] NOT NULL , ) The intention is that there will be multiple different classes of User that will utilize the accounts table. (Customers, Admin, Inventory). Now, when I created these tables, by accident, I put into the Accounts table a Foreign Key "Acct_Owner_GUID" not really thinking that the relationship between the Accounts ...

  1. #1

    Default SQL Newbie Primay-Foreign Key question

    I have 2 tables


    CREATE TABLE [dbo].[Accounts] (
    [Acct_GUID] [uniqueidentifier] NOT NULL ,
    [Login] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Pwd] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Acct_Owner_GUID] [uniqueidentifier] NULL ,
    ) ON

    and

    CREATE TABLE [dbo].[Customers] (
    [Cust_GUID] uniqueidentifier ROWGUIDCOL NOT NULL ,
    [Acct_GUID] [uniqueidentifier] NOT NULL ,
    )


    The intention is that there will be multiple different classes of User
    that will utilize the accounts table. (Customers, Admin, Inventory).

    Now, when I created these tables, by accident, I put into the Accounts
    table a Foreign Key "Acct_Owner_GUID" not really thinking that the
    relationship between the Accounts table and any "consumer" of it is a
    one-to-one relationship. A Customer has an Account.

    Now my question is, and it may be a little silly:

    Is there any situation where it is desirable to have two tables linked
    to each other, (i.e. A Customer has an Account and an Account has a User
    (which in this case would be Customer, but it could be Admin, etc.)) or
    is this just a nonsensical question?


    Any comments would be greatly appreciated.

    Jonathan Simms



    Jonathan Guest

  2. #2

    Default Re: SQL Newbie Primay-Foreign Key question


    You do NOT have any tables because you do NOT have any keys. Let's
    start by cleaning up this OO mess you gave us AND get into SQL AND a
    relational model instead.

    Ask yourself the question, "What can a GUID mean in the reality from
    which I constructed my data model?" They are a **physical** construct
    used for tion in the SQL Server implementation and have nothing
    to do with a data model. You then left out real keys and destroyed any
    hope of data integrity.

    You really have people with the same login? Well, you will!

    CREATE TABLE Accounts
    (login VARCHAR (20) NOT NULL PRIMARY KEY,
    pwd VARCHAR (20) NOT NULL, -- min length??
    acct_owner_guid uniqueidentifier NULL,
    ) on

     [/ref]
    User that will utilize the accounts table. (Customers, Admin,
    Inventory). <<

    All relationships are shown by values in the columns of tables. You
    need a column for this. Since you did not give god enough specs, here
    is a will guess at what you want. It gives a customer multiple logins
    with different classes of use.

    CREATE TABLE Users
    (cust_id INTEGER NOT NULL
    REFERENCES Customers(cust_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    login VARCHAR (20) NOT NULL
    REFERENCES Accounts (login)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    user_class CHAR(2) DEFAULT 'cs' NOT NULL
    CHECK(user_class IN ('cs', 'ad', 'in', )),
    PRIMARY KEY (cust_id, login));
     [/ref]
    linked to each other, (i.e. A customer has an account and an account has
    a user (which in this case would be customer, but it could be admin,
    etc.)) Or is this just a nonsensical question? <<

    There are no "links" in SQL; that is a term from assembly language and
    early, non-relational databases that used pointers. You have a lot of
    conceptual problems right now.

    But YES, YES, YES, you should have a lot relationships among the tables
    in the schema. And lots of DRI, column constraints, defaults, etc. The
    schema ought to enforce all the data model rules it can.

    The unit of work in SQL is the **entire schema** and you need to stop
    thinking that table = file, key = sequential record number, etc.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  3. #3

    Default Re: SQL Newbie Primay-Foreign Key question


    "Joe Celko" <edu> wrote in message
    news:phx.gbl... 

    Ah, i see what you mean by an OO mess.

    Thanks for your help


     [/ref]
    > User that will utilize the accounts table. (Customers, Admin,
    > Inventory). <<
    >
    > All relationships are shown by values in the columns of tables. You
    > need a column for this. Since you did not give god enough specs, here
    > is a will guess at what you want. It gives a customer multiple logins
    > with different classes of use.
    >
    > CREATE TABLE Users
    > (cust_id INTEGER NOT NULL
    > REFERENCES Customers(cust_id)
    > ON UPDATE CASCADE
    > ON DELETE CASCADE,
    > login VARCHAR (20) NOT NULL
    > REFERENCES Accounts (login)
    > ON UPDATE CASCADE
    > ON DELETE CASCADE,
    > user_class CHAR(2) DEFAULT 'cs' NOT NULL
    > CHECK(user_class IN ('cs', 'ad', 'in', .)),
    > PRIMARY KEY (cust_id, login));
    > [/ref]
    > linked to each other, (i.e. A customer has an account and an account has
    > a user (which in this case would be customer, but it could be admin,
    > etc.)) Or is this just a nonsensical question? <<
    >
    > There are no "links" in SQL; that is a term from assembly language and
    > early, non-relational databases that used pointers. You have a lot of
    > conceptual problems right now.
    >
    > But YES, YES, YES, you should have a lot relationships among the tables
    > in the schema. And lots of DRI, column constraints, defaults, etc. The
    > schema ought to enforce all the data model rules it can.
    >
    > The unit of work in SQL is the **entire schema** and you need to stop
    > thinking that table = file, key = sequential record number, etc.
    >
    > --CELKO--
    > ===========================
    > Please post DDL, so that people do not have to guess what the keys,
    > constraints, Declarative Referential Integrity, datatypes, etc. in your
    > schema are.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it![/ref]


    Jonathan Guest

  4. #4

    Default Re: SQL Newbie Primay-Foreign Key question

    Jonathan Simms (ccoomm) writes: 

    No, it is perfectly plausible, although not that common.

    A typical example would be a hierarchical relationship. Say that you
    have an Employees table that has a column Manager. Since Manager is
    another employess, this column would normally be foreign key in the
    same table. But say that for some reason we also have a table Managers
    which contains extra data about managers only. In this case
    Employees.Manager would point to Manager.EmployeeID which in its turn
    would point to Employees.EmpployeeID.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  5. #5

    Default Re: SQL Newbie Primay-Foreign Key question

    "Joe Celko" <edu> wrote in message
    news:phx.gbl... 

    Okay, first off, calm down. I said I'm a newbie and I'm using OO language
    because
    that's the only thing I really know well
     

    First off, GUID's are *not* simply for tion. They are a globally
    unique
    identifier. If i'm using GUID's as primary and foreign keys, so what? I'm
    simply
    guaranteeing that my foreign key is actually joining with the correct
    primary key.

    If i've got a table "Memo" and several other tables (Order, Customer,
    Supplier),
    and each one of those table's rows can have several memos, then how do you
    think I would go about retrieving all of the memos that a certain row owns?

    why wouldn't i use a GUID as the FK in the Memo table? Memo.FK_GUID would
    then refer to the Memo's owner. If I were using bigints for the other
    columns' identities
    there could easily be conflicts.



    Jonathan Guest

  6. #6

    Default Re: SQL Newbie Primay-Foreign Key question

    My personal experience with GUIDs is if you have more than 2 billion of
    something, use a GUID . If you have less, use an INT.
    INTs can be generated in sequence using an IDENTITY column. They're easier
    to type in, too.

    regards, -marty nicholson

    "Jonathan Simms" <ccoomm> wrote in message
    news:HxtSa.27064$srv.hcvlny.cv.net... 


    Martin Guest

  7. #7

    Default Re: SQL Newbie Primay-Foreign Key question

    >> If i'm using GUID's as primary and foreign keys, so what? I'm simply
    guaranteeing that my foreign key is actually joining with the correct
    primary key. <<

    You still think in OO terms. There is no OID concept in the relational
    model. A key -- BY DEFINITION -- is a subset of the attributes
    (columns) which uniquely identify each entity (row). It exists in the
    reality you are modeling, and can be verfied by going to that reality.
    They already exist; you do not create them apart from this reality.

    Let's look at your original table:

    CREATE TABLE Accounts
    (acct_guid UNIQUEIDENTIFIER NOT NULL,
    login VARCHAR (20) NOT NULL,
    pwd VARCHAR (20) NOT NULL,
    acct_owner_guid UNIQUEIDENTIFIER ); -- no DRI

    Insert the same row with a different UNIQUEIDENTIFIER 100,000 times.
    Insert rows with a different UNIQUEIDENTIFIER, the same login and
    multiple passwords.
     [/ref]
    the correct primary key. <<

    You are guaranteeing you will never know if you have the correct PK-FK
    relationships. Go to the Owners table and retain the same
    UNIQUEIDENTIFIER, but change the actual owners to 'Fred'.

    See how data integrity is destroyed? Keys are not sequential record
    numbers; keys are not OIDs.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Newbie Question: Biz Card Template Question
    By Thomas_Porter@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: May 30th, 08:08 AM
  2. A newbie with a newbie question
    By dusty_davis@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 5
    Last Post: October 14th, 07:54 AM
  3. Newbie Question
    By Pedro in forum Coldfusion - Getting Started
    Replies: 8
    Last Post: April 14th, 03:48 PM
  4. Replies: 5
    Last Post: December 3rd, 10:44 AM
  5. Pen Tool Use Question. (Embarrassingly Newbie Question)
    By Bozo Schmozo in forum Macromedia Flash
    Replies: 0
    Last Post: November 12th, 10:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139