Summary of index, foreign key, and field syntax maintenance for mysql, SQL Server, and oracle databases, and SQL Server oracle

Source: Internet
Author: User

Summary of index, foreign key, and field syntax maintenance for mysql, SQL Server, and oracle databases, and SQL Server oracle

Summary of index, foreign key, and field syntax maintenance for mysql, SQL Server, and oracle databases

 

1. MYSQL database 1) Create an index

Create index index_name ON table_name (column_list)

Create unique index index_name ON table_name (column_list)

Modify the table to add an index

Alter table table_name add index index_name (column_list)

Alter table table_name add unique (column_list)

Alter table table_name add primary key (column_list)

 

2) delete an index

Drop index index_name ON talbe_name

Delete an index by modifying the table

Alter table table_name drop index index_name

Alter table table_name DROP PRIMARY KEY

 

3) create a foreign key

Modify the table to add a foreign key

Alter table TABLE_NAME
   ADD [constraint foreign key name] foreign key (index_col_name ,...)
   REFERENCES tbl_name (index_col_name ,...)

Example:

Alter table TABLE_NAME add constraint FK_NAME (user_id)

REFERENCE sys_user (id)

Note that the data types of the two fields are consistent;

 

4) delete a foreign key

Alter table sys_org drop foreign key fk_s_o_id;

 

5) column operation syntax

Add the COLUMN birthday with the COLUMN keyword

Alter table sys_useradd COLUMN birthday char (19) not null;

MODIFY the column birthday with the MODIFY keyword

Alter table sys_usermodify birthday char (10 );

Delete the COLUMN birthday with the drop column keyword

Alter table sys_userdrop column birthday;

Modify the column name and attributes, change birthday to CSRQ, and change the attribute to char (10) and not null.

Alter table sys_userchange birthday CSRQ char (10) not null;

2. SQL SERVER database 1) Create an index

Create unique index un_index_name on sys_user (user_name );

2) delete an index

Drop index un_index_name ON sys_user

3) create a foreign key

Alter table sys_org add CONSTRAINT fk_s_o_id FOREIGN key (create_user)

REFERENCES sys_user (id );

The data type of the two fields must be consistent with that of mysql;

4) delete a foreign key

Alter table sys_org DROP constraint fk_s_o_id;

Different from mysql syntax

 

5) column maintenance syntax

6) ADD the column birthday with the ADD keyword

Alter table sys_user add birthday char (19) not null;

7) modify the column birthday attribute with the alter column keyword

Alter table sys_user altercolumn birthday char (10 );

8) Delete the COLUMN birthday with the drop column keyword

Alter table sys_user dropcolumn birthday;

9) modify the column name and attributes, change the birthday to CSRQ, and change the attribute to char (10) and not allow null not null. Perform the modification in two steps;

Exec sp_rename 'sys _ user. [birthday] ', 'srq', 'column ';

Alter table sys_user altercolumn CSRQ char (10) not null;

3. ORACLE database 1) Create an index

Create index index_name ON table_name (column_list)

Create unique index index_name ON table_name (column_list)

Create INDEX ind_s_u_sex on sys_user (sex );

2) delete an index

Drop index [schema.] indexname;

Drop index ind_s_u_sex;

3) create a foreign key

Modify the table to add a foreign key

Alter table TABLE_NAME
   ADD [constraint foreign key name] foreign key (index_col_name ,...)
   REFERENCES tbl_name (index_col_name ,...)

Example:

Alter table TABLE_NAME add constraint FK_NAME foreign key (user_id)

REFERENCE sys_user (id)

Note that the data types of the two fields are consistent;

 

4) delete a foreign key

Alter table TABLE_NAME drop constraint FK_NAME;

Example:

Alter table sys_org drop constraint fk_s_o_id;

5) column operation syntax

Add the column birthday with the add keyword

Alter table sys_user add birthday char (19) not null;

MODIFY the column birthday with the MODIFY keyword

Alter table sys_usermodify birthday char (10 );

Delete the COLUMN birthday with the drop column keyword

Alter table sys_user DROPCOLUMN birthday;

Modify the column name and attributes, change birthday to CSRQ, and change the attribute to char (10) or not allow null not null. The modification should be done in two steps;

ALTERTABLE sys_user rename column birthday to CSRQ;

Alter table sys_user MODIFYCSRQ char (10 );

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.