SQL Study Notes (21) ---------- add Foreign keys and Study Notes ----------

Source: Internet
Author: User

SQL Study Notes (21) ---------- add Foreign keys and Study Notes ----------

I have been busy with exams recently! As soon as I saw the exam, I was covered! Completely blank brain, uncontrolled, coupled with abnormal invigilators, let alone how painful it is. Fortunately, after successfully completing the test, I took some time out to learn about MySQL and Oracle and found some problems, which seemed very serious.

 

So, I want to ask: if you want to write SQL statements and add Foreign keys, how would you write them? If this is an interview question, how should you write it?

Anyway, I cannot write completely, and cannot write completely correctly. Usually we can directly set the primary key and foreign key through database graphics tools. However, it seems very difficult to write some SQL statements in real time! Therefore, I advise myself and readers to write some SQL statements with patience, which is helpful for finding a job. Of course, the code should be typed, but still, in practice, I feel the effect is good!

Accept!

Officially explain how to add a foreign key!

Add a foreign key to mysql

 
Add a foreign key to the added data table:Syntax: alter table name add constraint FK_ID foreign key (your foreign key field name) REFERENCES External table name (corresponding table's primary key field name );Example: alter table tb_active add constraint FK_ID foreign key (user_id) REFERENCES tb_user (id)// FK_ID is the name of the foreign key/*CREATE TABLE `tb_active` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `user_id_2` (`user_id`), CONSTRAINT `FK_ID` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1*/Delete foreign keySyntax: alter table table-name drop foreign key key-id;For example, alter table 'tb _ active' drop foreign key 'fk _ id'Automatic Key Update and deletion:The foreign key can ensure the integrity of the newly inserted record. However, what if the deleted record is named in the REFERENCES clause? What happens in a secondary table that uses the same value as a foreign key?   Obviously, those records should also be deleted. Otherwise, there will be many meaningless isolated records in the database. MYSQL can add an ONDELETEOr the on update clause simplifies the task. It tells the database how to handle isolated tasks in this case.  Keyword meaning CASCADE deletes all records that have reference relationships with deleted key values. Set null: modify all records that have reference relationships with the deleted key value and replace them with NULL values (only for Fields marked as not null) RESTRICT rejects the deletion request until the secondary table that uses the deleted key value is manually deleted without reference (this is the default setting and the safest setting) No action, NO ACTION  Note that on update and ONDELETERules: when MYSQL is set to enable automatic operations, if the key relationship is not set properly, it may cause serious data damage, For example, if a series of tables pass through the foreign key relationship and ONDELETEWhen a CASCADE rule is connected, any changes to the master table will cause some records that will be associated with the original deletion to be deleted without warning. Therefore, we also need to check these rules before the operation, and check again after the operation.Add foreign keyalter table locstock add foreign key locstock_ibfk2(stockid) references product(stockid)Locstock is the table name, locstock_ibfk2 is the name of the foreign key column in the first brackets of the foreign key name, product is the table name, and the second column is the name of the column associated with the foreign key. Delete foreign keyalter table locstock drop foreign key locstock_ibfk2 View the table's Foreign keysshow create table locstock [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)     REFERENCES tbl_name (index_col_name, ...)     [ONDELETE{RESTRICT | CASCADE | SET NULL | NO ACTION}]     [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]          All tables must be of the InnoDB type and cannot be temporary tables.· In the referenced table, an index is required, and the foreign key column is listed in the same order as the first column. If such an index does not exist, it must be automatically created in the reference table.· In the referenced table, there must be an index. The referenced columns are listed in the same order as the first column.· The index prefix of foreign key columns is not supported. One of the consequences is that BLOB and TEXT columns are not included in an external key, because the index of these columns must always contain a prefix length.· If CONSTRAINTsymbol is given, it must be unique in the database. If it is not given, InnoDB automatically creates this name.

  

 

 

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.