SQL Constraint Syntax and Use

This is a discussion on SQL Constraint Syntax and Use within the Tech Board forums, part of the Community Boards category; This is my first time using this site, and I have made various searches for SQL Database syntax and use ...

  1. #1
    Registered User
    Join Date
    Apr 2013
    Location
    Blackpool
    Posts
    1

    SQL Constraint Syntax and Use

    This is my first time using this site, and I have made various searches for SQL Database syntax and use but couldn't find a specific place to post this . . . I'm having trouble in SQL, with the table Players, it requires two primary keys. I'd added one successfully . . .
    Code:
                                              ALTER TABLE Players
    ADD CONSTRAINT fk_PlayerTypeID
    FOREIGN KEY (PlayerTypeID)
    REFERENCES PlayerTypes(PlayerTypeID)
    But when I come to do the same for TeamID . . .
    Code:
                                                                                                ALTER TABLE Players
    ADD CONSTRAINT fk_TeamID
    FOREIGN KEY (TeamID)
    REFERENCES Teams(TeamID)
    It says its already been created. I understand that it has already been created as a foreign key in the TeamLists table-how do I go about making it a foreign key in two tables? If that makes sense, any help would be appreciated!

  2. #2
    Registered User
    Join Date
    Oct 2006
    Posts
    2,457
    foreign key names (I'm guessing mysql) must be unique within a given schema.

  3. #3
    (?<!re)tired Mario F.'s Avatar
    Join Date
    May 2006
    Location
    Portugal
    Posts
    7,487
    As Elkvis says, if in your TeamLists table you named your foreign key with,

    ADD CONSTRAINT fk_TeamID

    Then you cannot use that name again.

    Ideally, in order to ensure unique names, you want to name your foreign keys in this format: fk_SecondaryTableName_PrimaryTableName_PrimaryTableKeyName

    SQL is unfortunately friendly to large names since there's virtually no name encapsulation in the language. With large databases this problem becomes sometimes hard to manage if you don't adhere to strict naming rules. Meanwhile SQL is one the few languages that still pretty much demands the use of Hungarian Notation (also one of the few places where it still makes sense these days). So don't ever drop those prefixes ("fk_, ix_, etc)
    Last edited by Mario F.; 04-23-2013 at 11:20 PM.
    The programmer’s wife tells him: “Run to the store and pick up a loaf of bread. If they have eggs, get a dozen.”
    The programmer comes home with 12 loaves of bread.


    Originally Posted by brewbuck:
    Reimplementing a large system in another language to get a 25% performance boost is nonsense. It would be cheaper to just get a computer which is 25% faster.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Help with some syntax
    By Alexis in forum C Programming
    Replies: 2
    Last Post: 02-17-2013, 05:27 PM
  2. Array Syntax Versus Pointer Syntax
    By LyTning94 in forum C++ Programming
    Replies: 6
    Last Post: 12-06-2011, 09:56 AM
  3. syntax help!!!!!!
    By 12oclock in forum C Programming
    Replies: 4
    Last Post: 02-22-2008, 04:56 PM
  4. map syntax
    By Perspective in forum C++ Programming
    Replies: 4
    Last Post: 01-25-2008, 09:35 AM
  5. Syntax Help Please
    By misplaced in forum C++ Programming
    Replies: 3
    Last Post: 04-22-2005, 07:47 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21