SQL PRIMARY KEY

9 Key excerpts on "SQL PRIMARY KEY"

• 

  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 - PDF

  Relational Database Design Clearly Explained

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

    (Publisher)

  Note: Notice how the preceding significantly distinguishes a re- lational database from one based on a navigational data model. You can retrieve any piece of data directly with only three pieces of information, whereas in a navigational database, a significant traversal of entity occurrences might be required to locate the same piece of data. Along with being unique, a primary key must not contain the value null. Null is a special database value meaning unknown. It is not the same as a zero or a blank. If you have one row with a null pri- mary key, then you are actually all right. However, the minute you introduce a second one, you have lost the property of uniqueness. We therefore forbid the presence of nulls in any primary key col- umns. This constraint, known as entity integrity, will be enforced by a DBMS whenever data are entered or modified. Selecting a good primary key can be a challenge. As you may re- member from Chapter 2, some entities have natural primary keys, such as purchase order numbers. These are arbitrary, meaningless, unique identifiers that a company attaches to the orders it sends to vendors and are therefore ideal primary keys. Primary Keys to Identify People What about a primary key to identify people? The first thing that pops into your mind might be a social security number. Every per- son in the United States over the age of 12 months has one, right? And the U.S. government assigns them so they are unique, right? Unfortunately, the answer to both questions is no. The Social Security Administration has been known to give every- one in an entire town the same SSN; over time, SSNs are reused. PRIMARY KEYS 79 However, these are minor problems compared to the issue of the so- cial security number being null. Consider what happens at a college that uses social security num- bers as student numbers when international students enroll. Upon entry into the country, the international students do not have social security numbers.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Beginning Database Design Solutions

  Understanding and Implementing Database Design Concepts for the Cloud and Beyond

  • Rod Stephens(Author)
  • 2023(Publication Date)
  • Wiley

    (Publisher)

  A unique key is an implementation issue, not a more theoretical concept like a candidate key is. 36 ❘ CHAPTER 2 RELATIONAL OVERVIEW A primary key is a superkey that is actually used to uniquely identify or find the rows in a table. A table can have only one primary key (hence the name “primary”). Again, this is more of an implemen- tation issue than a theoretical concern. Database products generally take special action to make finding records based on their primary keys faster than finding records based on other keys. Some databases allow alternate key fields to have missing values, whereas all of the fields in a primary key are required. For example, the Competitors table might have Name/Address/Event as a unique key and Name/Event as a primary key. Then it could contain a record with Name and Event but no Address value (although that would be a bit strange—we might want to require that all the fields have a value). An alternate key is a candidate key that is not the primary key. Some also call this a secondary key, although others use the term secondary key to mean any set of fields used to locate records even if the fields don’t define unique values. That’s a lot of keys to try to remember! The following list briefly summarizes the different flavors: ➤ Compound key or composite key—A key that includes more than one field. ➤ Superkey—A set of columns for which no two rows can have the exact same values. ➤ Candidate key—A minimal superkey. ➤ Unique key—A superkey used to require uniqueness by the database. ➤ Primary key—A unique key that is used to quickly locate records by the database. ➤ Alternate key—A candidate key that is not the primary key. ➤ Secondary key—A key used to look up records but that may not guarantee uniqueness.

  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)

  referential constraints . Referential constraints are important internal database objects that help maintain data integrity.

  Primary Key Constraints

  A primary key constraint on a column instructs the database engine to keep the entries in a column unique. For example, if we were to create a table with information about all the human beings on Earth, we could use the tongue print of human beings as unique identification. If tongue prints were in a column, it would be the primary key.

  It is possible to have a duplicate tongue print; however, it is rare. In such a case, you could create a primary key across multiple columns. Therefore, you could combine the tongue print, fingerprint, and the retinal signature to make a primary key. In such a case, the combination of these values in these columns should be unique across the table. In other words, there may be a duplicate tongue print, a duplicate fingerprint, and a duplicate retinal signature in the table—the database engine will allow that. However, there cannot be a duplicate combination of all three. Alternatively, there can be no two human beings whose tongue prints, fingerprints, and retinal signatures exactly match. This is called a 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

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Cloud Database Development and Management

  • Lee Chao(Author)
  • 2013(Publication Date)
  • Auerbach Publications

    (Publisher)

  For example, to make the column ProductID the primary key, use the SQL statement: ProductID INT PRIMARY KEY A constraint enforced on a single column is classified as a column constraint. If a primary key is defined on multiple columns, it is called a combination primary key and you need to use the keyword CONSTRAINT to define it. The syntax for defining a primary key by using the keyword CONSTRAINT is CONSTRAINT constraint_name PRIMARY KEY (multiple_column_names) To illustrate the use of the keyword CONSTRAINT, consider the example that defines the combination primary key on the columns OrderID and InventoryID: CONSTRAINT OrderID_InventoryID_pk PRIMARY KEY (OrderID, InventoryID) If a constraint is enforced on multiple columns, it is called a table constraint. When more than one column is included in a constraint, you should use the table constraint. b. Foreign Key Constraint : A foreign key constraint can be defined on a single column or multiple columns. When defining it on a single column, you can use either an unnamed or a named constraint. For an unnamed foreign key constraint, consider the following example that makes the column ProductID the foreign key: FOREIGN KEY (ProductID) REFERENCES PRODUCT (ProductID) The word ProductID after the keyword phrase FOREIGN KEY is the foreign key col-umn name in the child table. The parent table name and the primary key column in the parent table are placed behind the keyword REFERENCES. You can also use a named foreign key constraint by using the keyword CONSTRAINT. When a foreign key is defined on multiple columns, you should use a named foreign key constraint. The syntax for the named foreign key constraint is CONSTRAINT constraint_name FOREIGN KEY (fk_column_name) REFERENCES parent_table_name (primary_key_column) The constraint name is a character string. To make the name meaningful, you can name the constraint as TableName_ColumnName_fk. The keyword phrase FOREIGN KEY

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Theory and Practice of Relational Databases

  • Stefan Stanczyk, Bob Champion, Richard Leyton(Authors)
  • 2003(Publication Date)
  • CRC Press

    (Publisher)

  Expression Integrity. Formulating unambiguously certain queries would not be possible (joining a table with itself is a prime example since this operation carries a risk of table corruption) without using a substitute name for a table. SQL enforces Entity Integrity through specification of constraints. A constraint set on the prime attribute(s) ensures both the uniqueness and existence of the primary key—any attempt to insert a duplicate value for the primary key or to insert a record with a null value for it will necessarily fail. Furthermore, any attribute in a table can be restricted (if needed) not to accept null values. In the current version of SQL the principle of Referential Integrity is only partly supported. A mechanism is provided to inform the DBMS about the existence of the foreign keys and their correspondence to the relevant primary keys. The constraint definition can also inform the DBMS whether or not deletion of referenced records is automatically required should the corresponding primary key be deleted (cascade deletion). Provision for maintaining referential integrity with respect to insertion is much weaker—records containing a foreign-key value that does not match the corresponding primary key will not be inserted. Apart from general integrity constraints, the application-dependent particular integrity rules can be enforced through check-constraints that explicitly define conditions which 1} number (length) fixed point number (no fraction part) number=float floating point number with length=38 date range {01–Jan–4712 BC, 31–Dec 4712 AD} default format ‘dd-mmm-yy’ long variable length string, max 2 31 -1 B one column per table, non-indexable, search-inactive, no integrity constraints can be defined on (except not null) Structured query language 141 must be obeyed by all the tuples in the table. The check-constraint can refer to any attribute in the table but cannot refer to the attributes of other tables.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Inside Symbian SQL

  A Mobile Developer's Guide to SQLite

  • Ivan Litovski, Richard Maynard(Authors)
  • 2010(Publication Date)
  • Wiley

    (Publisher)

  This feature was added for specific DATA INTEGRITY 121 applications that required this behavior. Unless you have such a specific need in your application, it is perhaps best to use INTEGER PRIMARY KEY for autoincrement columns. Like UNIQUE constraints, PRIMARY KEY constraints can be defined over multiple columns. You don’t have to use an integer value for your primary key. If you choose to use another value, SQLite still maintains the ROWID column internally, but it also places a UNIQUE constraint on your declared primary key. For example: sqlite> CREATE TABLE pkey(x text, y text, PRIMARY KEY(x,y)); sqlite> INSERT INTO pkey VALUES ('x','y'); sqlite> INSERT INTO pkey VALUES ('x','x'); sqlite> SELECT ROWID, x, y FROM pkey; rowid x y ---------- ---------- ---------- 1 x y 2 x x sqlite> INSERT INTO pkey VALUES ('x','x'); SQL error: columns x, y are not unique The primary key here is technically just a UNIQUE constraint across two columns, nothing more. As stated before, the concept of a primary key is more or less just lip service to the relational model – SQLite always provides one whether you do or not. If you do define a primary key, it is in reality just another UNIQUE constraint, nothing more. 4.7.2 Domain Integrity The simplest definition of domain integrity is the conformance of a col- umn’s values to its assigned domain. That is, every value in a column should exist within that column’s defined domain. However, the term domain is a little vague. Domains are often compared to types in pro- gramming languages, such as strings or floats. And while that is not a bad analogy, domain integrity is actually much broader than that. Domain constraints make it possible for you to start with a simple type – such as an integer – and add additional constraints to create a more restricted set of acceptable values for a column. For example, you can create a column with an integer type and add the constraint that only three such values are allowed: {−1, 0.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Handbook of Data Management

  1999 Edition

  • Sanjiv Purba(Author)
  • 2019(Publication Date)
  • Auerbach Publications

    (Publisher)

  There are two basic key column constraints that can be applied to table columns with the CREATE TABLE and the ALTER TABLE commands, namely PRIMARY KEY and FOREIGN KEY. There can be no more than one PRIMARY KEY constraint for each table and no more than 31 FOREIGN KEY constraints for each table. The PRIMARY KEY constraint is used to enforce key column uniqueness in a table. When it is specified, two rows are not allowed to exist within the same table with identical primary keys. Once a column is specified as a PRIMARY KEY, it cannot be saved with a NULL value. Key constraints can be created with specific column names or as separate objects.

  PRIMARY KEY Syntax:

            CREATE TABLE table_name          (            [column_list datatype ...] [PRIMARY KEY            [CLUSTERED/NONCLUSTERED]]          )      OR          CREATE TABLE table_name          (            [column_list datatype ...],            CONSTRAINT PK_constraint_name PRIMARY KEY            [CLUSTERED/NONCLUSTERED] (column1, column2, ...)          )

  Notes: A clustered index is created by default on the column specified as PRIMARY KEY. At most, only one “column_name” can be associated with a PRIMARY KEY in one table. Examples:

  1. This example creates the “cityjist” table, specifying the “city_code” field as an IDENTITY column and as a primary key column. This example also indicates that the column should generate a clustered index for the table. The example also extracts the inserted data from the “cityjist” table.
     USE addressgodrop table city_listgocreate table city_list(    city_code     int IDENTITY (1000,1) PRIMARY KEY                  CLUSTERED,    description   char(80))gocreate table city_list(    city_code     int IDENTITY (1000,1) PRIMARY KEY                  CLUSTERED,    description   char(80))go/* After parsing the code without errors into thé address database, insert a row into the table */insert city_list    (description)values    (“this is an example”)go
     This insert batch can be executed recursively, adding a new record to the “cityjist” table each time. Because the PRIMARY KEY field was created with an IDENTITY feature, a new code is generated starting at 1000 with the first insert. This allows each row to be unique. A join against two system tables, sysobjects, and sysindexes can be used to confirm that a clustered index was created in response to the primary key feature. As shown in the following code, an object ID joins the two tables together. In this example, the number of rows in the index is displayed. As an example, the insert code was executed four times, and then the join code was executed. The number of rows retrieved by the join is four. Another insert was then executed to insert a fifth row into the “city_code” table. The join showed that five rows were indexed.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Developing Windows-Based and Web-Enabled Information Systems

  • Nong Ye, Teresa Wu(Authors)
  • 2014(Publication Date)
  • CRC Press

    (Publisher)

  The optional PRIMARY KEY constraint specifies the list of columns in the table that forms the primary key, separated by commas. The optional FOREIGN KEY constraint is used to list the col-umns within the table as the foreign key, followed by the REFERENCES constraint, which specifies the other table where the foreign key columns are linked. Example 8.7 Use CREATE TABLE statement to create three relations: Drugs, Stores, and Sales: CREATE TABLE Drugs ( Drug_ID char (20) NOT NULL , Name varchar (255), Price numeric (10, 2), Description varchar (255), CHECK ( Price > 0), PRIMARY KEY ( Drug_ID )) CREATE TABLE Stores ( Store_ID char (20) NOT NULL , Name varchar (255), Phone_Number varchar (10), PRIMARY KEY ( Store_ID )) CREATE TABLE Sales ( Transaction_ID char (20) NOT NULL , Drug_ID char (20), Quantity int DEFAULT 0, Store_ID char (20), PRIMARY KEY ( ST_ID ) FOREIGN KEY ( Store_ID ) REFERENCE Stores ( Store_ID ) FOREIGN KEY ( Drug_ID ) REFERENCE Drugs ( Drug_ID )) Example 8.8 Add the Drugs table element to the myschema created in Example 8.2: CREATE SCHEMA myschema AUTHORIZATION joe CREATE TABLE Drugs ( Drug_ID char (20) NOT NULL , Name varchar (255), Price numeric (10, 2), Description varchar (255), CHECK ( Price > 0), PRIMARY KEY ( Drug_ID )) Now, we have learned to create schema, table. We will learn how to interact (retrieve, modify) the data in the next section. 157 Structured Query Language 8.3.2 SQL Data Manipulation Language: Data Queries 8.3.2.1 Basic Structure of the SELECT Command A relational database consists of a collection of relations (tables), each of which is assigned with a unique name. Each relation has a structure similar to that presented in Chapter 5. To create a query or a report that spans tables and to take in a subset of attributes, the SQL SELECT-FROM clause is used.

  Sign up to read

  Learn more about book