Get a Jumpstart with our Sample Apps
Results 1 to 2 of 2

Thread: SQL PK and FK Questions

  1. #1
    Member
    Real Name
    C. Michael Roberson
    Join Date
    Aug 2009
    Posts
    56

    Default SQL PK and FK Questions

    I am new to SQL and designing a new database/webapp using A5V10.5 as the front end. I have designed desktop databases using A5v10.5 with DBF files before but, as I said, new to SQL and new to webapps. I came to the Alpha Community with my questions because I know many of you use SQL tables as a back end database for A5. I am using MariaDB which is much like MySQL, and Navicat to design using a GUI. At any rate, here is what I am doing and what I need to know (for right now).
    Perhaps the best way to represent he table relationships would be as follows:

    salesorders (the main table, has 13 columns; do not need to list all of them)
    salesorders.sonumber (pk)
    salesorders.ponumber
    salesorders.custname

    The other eight tables are(table name in bold):

    1/ Customer customer.custid (pk)
    customer.sonumber(fk) > child link to salesorders.sonumber(pk)

    2/ custaddr custaddr.custid(pk) > child link to customer.custid(pk)

    The above (italics) is one of my questions. The child link of custaddr.custid(pk) to customer.custid(pk) is two primary keys linked where custaddr.custid is both the pk and fk for that table. Can I do that? Can I link two primary keys?

    3/ ponumber ponumber.sonumber(pk) > child link to salesorders.sonumber(pk)
    ponumber.ponumber

    4/ hwsup hwup.sonumber(pk) > child link to salesorders.sonumber(pk)

    5/ swsup swsup.sonumber(pk) > child link to salesorders.sonumber(pk)

    6/ instlsup instlsup.sonumber(pk) > child link to salesorders.sonumber(pk)

    7/ hwprodshwprods.ponumber(pk) > child link to ponumber.ponumber

    8/ swprods swprods.ponumber(pk) > child link to ponumber.ponumber

    As can be seen above, I have several primary keys in child tables (functioning as foreign keys) linking to primary keys in parent tables. I need to know if I can do this.

    In addition, the ponumber table has only two columns, sonumber(pk) and ponumber. The sonumber column really needs to be a fk referenced back to the salesorders.sonumber (pk) to establish a one-to-many parent/child relationship with salesorders table being the parent and ponumber being the child. As such, the ponumber table will have more than one occurrence of the sonumber (not unique) so the sonumber cannot really be a primary key in the ponumber table. The question here is, can the sonumber column be a foreign key and not be unique? Or, in other words, does a foreign key in a table have to be unique?

    gitpicker

  2. #2
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,832

    Default Re: SQL PK and FK Questions

    You may have put this in the wrong form. If it is database only, this or database design. If this is for web, you might try Application Server Version 10 - Web/Browser Applications forum. they do more sql there.

    You might wish to rethink your design a bit. Unless there are solid reasons, it is no normal to have 5 PK in one table.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

Similar Threads

  1. Update Active Link Table and SQL reports SQL Statements from Xbasic?
    By compuaid in forum Alpha Five Version 11 - Desktop Applications
    Replies: 0
    Last Post: 05-01-2012, 08:27 AM
  2. Translating T-SQL to Portable SQL using SQL Genie
    By azz211 in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 08-19-2011, 02:31 AM
  3. SQL server questions
    By greyscale in forum General Questions
    Replies: 3
    Last Post: 07-10-2011, 02:27 PM
  4. questions about converting to SQL
    By Steve Wood in forum Application Server Version 9 - Web/Browser Applications
    Replies: 10
    Last Post: 05-29-2009, 05:50 PM
  5. Questions on SQL database features
    By mumfie in forum Application Server Version 9 - Web/Browser Applications
    Replies: 3
    Last Post: 12-31-2008, 05:56 PM

Posting Permissions

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