Defining a primary key

It is a good practice to define a primary key for a data table by using the primary key to uniquely locate a data record and to have the primary key associated with the data table when the foreign key is associated. Defining a primary key in Create TABLE is done through the primary key keyword, where the defined position is after all field definitions. For example, we set up a data table for the bus, this table has the bus number Fnumber, the driver name Fdrivername, put into use the number of years fusedyears and other fields, where the bus number fnumber field to be defined as the primary key, then as long as the following design to build the table sql:

Mysql,mssqlserver:CREATETABLE T_bus (FnumberVARCHAR (), FdrivernameVARCHAR (), Fusedyearsint,primary KEY (FNumber)) Oracle:create table T_bus (Fnumber VARCHAR2 (20), Fdrivername VARCHAR2 (20), Fusedyears NUMBER (10), primary KEY (FNumber)) DB2:< Span class= "Hljs-keyword" >create table t_bus (fnumber VARCHAR (20) not NULL,FDriverNamevarchar (20), Fusedyears INT,primary key (fnumber))        

As you can see, the primary key definition is defined in the "constraint definition segment" After all fields, in the form of primary key (primary key field name), and in some database systems the parentheses on either side of the primary key field name can be omitted, that is, the primary key Fnumber can be written, However, in order to be able to better cross-database, it is recommended not to use this non-generic wording.

Note that in the Create TABLE statement for the DB2 database listed above, we set a non-null constraint for the Fnumber field. Because in DB2, the primary key field must be added as a non-null constraint, otherwise it will be reported that a "fnumber" cannot be a column primary key or unique key because it can contain null values. "Error.

Sometimes there is no single primary key in the data table, for example, an industry association needs to create a table to hold personal membership information, the table records the company name Fcompanyname, company internal work number Finternalnumber, name fname, etc., due to the existence of the same name , so you can not use the name as the primary key, also because the internal work number between the company may also be duplicated, so you can not use the company's internal work number as the primary key. However, if the company name is determined, then the company's internal work number is unique, that is, by the company name Fcompanyname and the company internal work number Finternalnumber two fields together can uniquely identify a personal member, we can let Fcompanyname, Finternalnumber Two fields are joined together as primary keys, such that the primary key is called the Federated Primary Key (or the composite primary key). You can have two or more fields as a federated primary key, which solves the problem of not having a single primary key field in a table. Defining a federated primary key is similar to a unique primary key, as long as the individual fields in the parentheses after primary key are listed as the Union primary key. The above example builds the table SQL as follows:

MYSQL,MSSQLSERVER,DB2:CREATETABLE T_personalmember (fcompanynameVARCHAR (), FinternalnumberVARCHAR (), FNameVARCHAR (20),PRIMARYKEY (Fcompanyname,finternalnumber)) Oracle:CREATEtable t_personalmember (fcompanyname VARCHAR2 (20), Finternalnumber VARCHAR2 (20), FName VARCHAR2 (20), primary key (Fcompanyname,finternalnumber)) DB2: create table t_personalmember (FCompanyName varchar (20) not  Null,finternalnumber varchar (20) not null,fname VARCHAR (20), primary key (FCompanyName, Finternalnumber)               

It is also important to note that each field that makes up a federated primary key in DB2 must also be added with a non-null constraint. Using a federated primary key solves the problem that there are no unique primary key fields in the table, but the Federated primary Key has the following drawbacks:

1, low efficiency. In the process of adding, deleting, locating and updating data, the database system must handle two fields, which greatly reduces the processing speed.

2, making the database structure design worse. The fields that make up the federated primary key are usually fields of business meaning that conflict with the best practices of using logical primary keys instead of business primary keys, which can easily lead to system development and maintenance headaches.

3, making it cumbersome to create foreign key associations to this table even if you cannot create a foreign key association relationship to this table.

4, to increase the difficulty of development. Many development tools and frameworks only have good support for Tanki, and very complex special handling is often required for federated primary keys.

With these drawbacks in mind, we should only use federated primary keys for special occasions, such as legacy systems, and we should use unique primary keys on other occasions.

Defining a primary key