Linux SQL statement Concise tutorial---Primary key, foreign key

primary KEY (Primary key) each of the data in the table is a unique value in the form. In other words, it is a unique way to identify each row of data in a table. The primary key can be a field in the original data, or a man-made field (a field that has no relation to the original data). A primary key can contain one or more fields. When a primary key contains more than one field, it is called a key combination (Composite key).

The primary key can be set when a new table is built (using the CREATE Table statement ) or by changing the existing table schema (using ALTER TABLE).

Here are a few ways to set a primary key when you are building a new table:

MySQL:

CREATE TABLE Customer
(SID Integer,
last_name varchar (30),
first_name varchar (30),
PRIMARY KEY (SID));

Oracle:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
last_name varchar (30),
First_Name varchar (());

SQL Server:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
last_name varchar (30),
First_Name varchar (());

Here's how you can set the primary key by changing the existing table schema:

MySQL:

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Oracle:

ALTER TABLE Customer ADD PRIMARY KEY (SID);

SQL Server:

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Note that before adding the primary key with the ALTER TABLE statement, we need to make sure that the field used as the master key is set to "not NULL"; that is, the field must not have data.

FOREIGN Key is a (or several) field that points to another table's primary key. The purpose of the foreign key is to determine the reference integrity of the data (referential integrity). In other words, only permitted data values will be stored in the database.

For example, suppose we have two tables: a customer table that records all of the customer's data, and another orders table that records all the customer's orders. One of the limitations here is that all the customers in the order information must be in the Customer table. Here, we will set a foreign key in the ORDERS table, and this foreign key is the primary key in the CUSTOMER table. In this way, we can determine that all customers in the ORDERS table exist in the Customer table. In other words, there cannot be any customer in theORDERS table that does not exist in the customer table.

The structure of these two tables will be as follows:

CUSTOMER table

Field name	Properties
Sid	Primary key
Last_Name
First_Name

ORDERS form

Field name	Properties
order_id	Primary key
Order_date
Customer_sid	FOREIGN key
Amount

In the example above, the Customer_sid field in theORDERS table is a foreign key that points to the SID field in the Customer table.

The following is a list of several ways to specify foreign keys when you are building an ORDERS table:

MySQL:

CREATE TABLE ORDERS
(order_id Integer,
Order_date Date,
Customer_sid Integer,
Amount Double,
PRIMARY KEY (order_id),
FOREIGN KEY (CUSTOMER_SID) REFERENCES Customer (SID));

Oracle:

CREATE TABLE ORDERS
(order_id integer PRIMARY KEY,
Order_date Date,
Customer_sid integer REFERENCES CUSTOMER (SID),
Amount double);

SQL Server:

CREATE TABLE ORDERS
(order_id integer PRIMARY KEY,
Order_date datetime,
Customer_sid integer REFERENCES CUSTOMER (SID),
Amount double);

The following example specifies a foreign key by changing the table schema. This assumes that the ORDERS table has been built and the foreign key has not been specified:

MySQL:

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES Customer (SID);

Oracle:

ALTER TABLE ORDERS
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES Customer (SID);

SQL Server:

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES Customer (SID);

SQL DROP TABLE >>

Reprint please specify: Xiao Liu



Linux SQL statement Concise tutorial---Primary key, foreign key