Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

Introduction to the

Database is the foundation of all our applications, no database program is not a good program, under normal circumstances we are through visual tools to create databases and database tables, today to introduce a little different, use command line tools to create database primary key and foreign key.

This section describes three common databases: mysql, oracle, and sqlserver.

Create a primary key

A primary key is a field in a database that is used as the primary index.

Suppose our data table is named Customer and has three fields: SID, Last_Name, and First_Name. Let’s look at the difference between creating a field in each database:

MySQL

Create database table:

CREATE TABLE Customer 
(SID integer, 
name varchar(30), 
phone varchar(30), 
PRIMARY KEY (SID)); 
Copy the code

Create primary key:

ALTER TABLE Customer ADD PRIMARY KEY (SID); 
Copy the code

Oracle

Create database table:

CREATE TABLE Customer 
(SID integer PRIMARY KEY, 
name varchar(30), 
phone varchar(30));
Copy the code

Create primary key:

ALTER TABLE Customer ADD PRIMARY KEY (SID); 
Copy the code

SQL Server

Create database table:

CREATE TABLE Customer 
(SID integer PRIMARY KEY, 
name varchar(30), 
phone varchar(30)); 
Copy the code

Create primary key:

ALTER TABLE Customer ADD PRIMARY KEY (SID); 
Copy the code

Create a foreign key

A foreign key represents a database association. Above, we created the Customer table with a SID field in it. Now we create another ORDERS table and need to reference the SID field of the Customer table as a foreign key. So let’s see what we can do.

mysql

First create the table:

CREATE TABLE ORDERS 
(ID integer, 
Create_date date, 
Customer_SID integer, 
Amount double, 
Primary Key (ID), 
Foreign Key (Customer_SID) references CUSTOMER(SID)); 
Copy the code

Create a foreign key:

ALTER TABLE ORDERS 
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid); 
Copy the code

Oracle

First create the table:

CREATE TABLE ORDERS 
(ID integer primary key, 
Create_date date, 
Customer_SID integer references CUSTOMER(SID), 
Amount double); 
Copy the code

Create a foreign key:

ALTER TABLE ORDERS 
ADD (CONSTRAINT fk_orders) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid); 
Copy the code

SQL Server

First create the table:

CREATE TABLE ORDERS 
(ID integer primary key, 
Create_date datetime, 
Customer_SID integer references CUSTOMER(SID), 
Amount double); 
Copy the code

Create a foreign key:

ALTER TABLE ORDERS 
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid); 
Copy the code

conclusion

These are the basic operations for creating primary and foreign keys in three databases, have you learned?

This article is available at www.flydean.com/01-db-prima…

The most popular interpretation, the most profound dry goods, the most concise tutorial, many tips you didn’t know waiting for you to discover!

Welcome to pay attention to my public number: “procedures those things”, understand technology, more understand you!