SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Foreign Key Syntax

    Creating new tables and want to get the Foreign Key syntax correct. (using MySQL 5.0) Is the code below correct?

    Code MySQL:
    CREATE TABLE company
    (
        id                  INT(11)         NOT NULL    AUTO_INCREMENT  PRIMARY KEY
    ,   company             VARCHAR(100)
    ,   first_name          VARCHAR(50)
    ,   last_name           VARCHAR(50)
    ,   address             VARCHAR(100)
    ,   address2            VARCHAR(100)
    ,   city                VARCHAR(100)
    ,   state               VARCHAR(50)
    ,   zip                 VARCHAR(10)
    ,   phone               VARCHAR(25)
    ,   cell_phone          VARCHAR(25)
    ,   fax                 VARCHAR(25)
    ,   email               VARCHAR(50)
    ,   website             VARCHAR(50)
    ,   license             VARCHAR(50)
    ,   insurance_carrier   VARCHAR(100)
    ,   insurance_phone     VARCHAR(50)
    ,   forms_email         VARCHAR(50)
    )
        DEFAULT CHARACTER SET utf8 ENGINE=InnoDB
    ;
     
     
    CREATE TABLE users
    (
        id                  INT(11)         NOT NULL    AUTO_INCREMENT  PRIMARY KEY
    ,   company_id          INT(11)
    ,   name                VARCHAR(50)     NOT NULL
    ,   email               VARCHAR(50)     NOT NULL
    ,   password            CHAR(32)        NOT NULL
    ,   role                CHAR(5)         NOT NULL
     
    ,   CONSTRAINT          email_uk        UNIQUE      (  email  )
     
    ,   CONSTRAINT          FOREIGN KEY (company_id) REFERENCES company (id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
    )
        DEFAULT CHARACTER SET utf8 ENGINE=InnoDB
    ;


    Any comments would be appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Lyle Workman View Post
    Is the code below correct?
    looks okay to me

    however, the best way to determine whether your syntax is any good ...

    ... is to test it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •