SQL FOREIGN KEY

4 Key excerpts on "SQL FOREIGN KEY"

• 

  eBook - ePub

  Relational Database Design and Implementation

  Clearly Explained

  • Jan L. Harrington(Author)
  • 2009(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  The procedure described in the preceding paragraph works very well—unless for some reason there is no order number in the orders table to match a row in the order items table. This is a very undesirable situation because you can't ship the ordered items if you don't know which customer placed the order.

  This relational data model therefore enforces a constraint called referential integrity, which states that every non-null foreign key value must match an existing primary key value. Of all the constraints on a relational database, this is probably the most important because it ensures the consistency of the cross-references among tables.

  Referential integrity constraints are stored in the database and enforced automatically by the DBMS. As with all other constraints, each time a user enters or modifies data, the DBMS checks the constraints and verifies that they are met. If the constraints are violated, the data modification will not be allowed.

  Foreign Keys and Primary Keys in the Same Table

  Foreign keys do not necessarily need to reference a primary key in a different table; they need only reference a primary key. As an example, consider the following employee relation:

  employee (employee_ID , first_name, last_name, department, manager_ID)

  A manager is also an employee. Therefore, the manager ID, although named differently from the employee ID, is actually a foreign key that references the primary key of its own table. The DBMS will therefore always ensure that whenever a user enters a manager ID, that manager already exists in the table as an employee.

  Views

  The people responsible for developing a database schema and those who write application programs for use by technologically unsophisticated users typically have knowledge of and access to the entire schema, including direct access to the database's base tables. However, it is usually undesirable to have end users working directly with base tables, primarily for security reasons.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Programming Interviews Exposed

  Coding Your Way Through the Interview

  • John Mongan, Noah Suojanen Kindler, Eric Giguère(Authors)
  • 2018(Publication Date)
  • Wrox

    (Publisher)

  One of the keys is usually designated the primary key. Each row in the table must have a value for the primary key, and each of these values must be unique. For example, in a table of employees, you might use the employee identification number—guaranteed to be unique for each employee—as the primary key. When the data being stored does not naturally contain 13 204 ❘ CHAPTER 13 DATABASES guaranteed unique values that can be used as primary keys, the database is often configured to automatically assign a unique serial numbered value as the primary key for each row inserted in the table. A table can be linked to another table using a foreign key. A foreign key is a column where the values match values from a key column in the other table (usually the primary key). When every foreign key value exists as a key in the table it references, the database has referential integrity. This can be enforced through the use of foreign key constraints. Depending on how the constraints are configured, an attempt to delete a row with a key value that exists in another table as a foreign key is either prevented or causes deletion or modification of the rows that reference it. The most common way to manipulate and query databases is through the use of Structured Query Language (SQL). Some variations in syntax exist across different database management systems (DBMS), particularly for advanced features, but the basic syntax is fairly consistent. SQL SQL is the lingua franca of relational database manipulation. It provides mechanisms for most kinds of database manipulations. Understandably, SQL is a big topic, and numerous books are devoted just to SQL and relational databases. Nevertheless, the basic tasks of storing and retrieving We begin with the following schema: Player ( name CHAR(20), number INTEGER(4) ); Stats ( number INTEGER(4), totalPoints INTEGER(4), year CHAR(20) ); Table 13-1 shows some sample data for Player, and Table 13-2 shows sample Stats data.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  The SQL Workshop

  A New, Interactive Approach to Learning SQL

  • Frank Solomon, Prashanth Jayaram, Awni Al Saqqa(Authors)
  • 2019(Publication Date)
  • Packt Publishing

    (Publisher)

  composite primary key .

  Foreign Key Constraints

  Let's look at this in the context of a primary key. When this primary key is referenced by a column in another table, this primary key becomes the foreign key of the other table. For example, consider the previously created database PACKT_ONLINE_SHOP :

  DROP DATABASE IF EXISTS PACKT_ONLINE_SHOP; CREATE DATABASE IF NOT EXISTS PACKT_ONLINE_SHOP; USE PACKT_ONLINE_SHOP; CREATE TABLE Customers (     CustomerID INT NOT NULL AUTO_INCREMENT,     FirstName CHAR(50) NOT NULL,     LastName CHAR(50) NOT NULL,     Address CHAR(250) NULL,     Email CHAR(200) NULL,     Phone CHAR(50) NULL,     Notes VARCHAR(750) NULL,     BalanceNotes VARCHAR(750) NULL,     PRIMARY KEY (CustomerID) ); CREATE TABLE Orders (     OrderID INT NOT NULL AUTO_INCREMENT,     CustomerID INT NOT NULL,     OrderNumber CHAR(50) NOT NULL,     OrderDate DATETIME NOT NULL,     ShipmentDate DATETIME NULL,     OrderStatus CHAR(10) NULL,     Notes VARCHAR(750) NULL, PRIMARY KEY (OrderID), FOREIGN KEY FK_Customer_CustomerID(CustomerID) REFERENCES Customers(CustomerID) );

  Each customer can place one or more orders. Each order can be for one or more products. Each order belongs to only one customer. When the database is designed, the relationship is established between the customers and the orders with a one-to-many relationship. There is also a relationship between orders and products, with a cardinality ratio of many-to-many (because one order can contain many products, and many copies of one product can be sold through many orders). The Orders table contains the CustomerID column, which refers to the CustomerID of the Customers table. The CustomerID field (or column) in the Customers table is the primary key, but it is the foreign key for the Orders table. The FOREIGN KEY clause specifies the foreign key using the REFERENCES clause. In this context, Customers could be considered the parent table and Orders

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Relational Database Design Clearly Explained

  • Jan L. Harrington(Author)
  • 2002(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Of all the constraints on a rela- tional database, this is probably the most important because it en- sures the consistency of the cross-references among tables. Referential integrity constraints are stored in the database and en- forced automatically by the DBMS. As with all other constraints, each time a user enters or modifies data, the DBMS checks the con- straints and verifies that they are met. If the constraints are violated, the data modification will not be allowed. 86 THE RELATIONAL DATA MODEL Foreign Keys and Primary Keys in the Same Table Foreign keys do not necessarily need to reference a primary key in a different table; they need only reference a primary key. As an ex- ample, consider the following employee relation: employee (employee ID, first name, last name, department, manager ID) A manager is also an employee. Therefore, the manager ID, al- though named differently from the employee ID, is actually a for- eign key that references the primary key of its own table. The DBMS will therefore always ensure that whenever a user enters a manager ID, that manager already exists in the table as an employee. Views The people responsible for developing a database schema and those who write application programs for use by technologically unso- phisticated users typically have knowledge of and access to the en- tire schema, including direct access to the database's base tables. However, it is usually undesirable to have end users working di- rectly with base tables, primarily for security reasons. The relational data model therefore includes a way to provide end users with their own window into the database, one that hides the details of the overall database design and prohibits direct access to the base tables. The View Mechanism A view is not stored with data. Instead, it is stored under a name in the data dictionary along with a database query that will retrieve its data.

  Sign up to read

  Learn more about book