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 include one or more fields. When a primary key includes 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 change the existing table schema setting (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);

Please note that. Before using the ALTER TABLE statement to join the primary key, we need to confirm that the field used as the main key is set to "not NULL"; that is, the field must not have no 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 integrity of the data (referential integrity).

Other words. Only authorized data values will be stored in the database.

For example. If we have two tables: a CUSTOMER table. It records all the customer's information, and an orders form 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 form.

Here, we will set a foreign key in the ORDERS table, and this foreign key is the primary key in the CUSTOMER table. So. We are able to determine that all customers in the ORDERS table are present in the Customer table. In other words, theORDERS form cannot have any information that no customer is in the customer's form.

The structure of these two tables will be such as the following:

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. Here if the ORDERS table is already 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

?

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