Sqlserverql statement: delete Foreign keys

Source: Internet
Author: User
Sqlserverql statement: delete Foreign keys and delete alter & nbsp; table & nbsp; tablename & nbsp; add & nbsp; constraint & nbsp; & nbsp; ordersrelationship & nbsp; & SQL server ql statement deletes Foreign keys and deletes
Alter table tablename add constraint ordersrelationship
Foreign key mployeeid
References employees (employeeid)
On delete cascade
On update cascade

Alter table books add constrait external health name -- specify the following name
Add foreign key (isbn) references readers (readerid );
Alter table books drop constraint


The methods and steps for setting the authorid in mybbs as the SQL foreign key are described in detail.

Set the authorid In the table mybbs as the foreign key of SQL server. Use the transact SQL statement directly by referring to the id field of the author table. The procedure is as follows:

-- Add the SQL server foreign key constraint fk_mybbs_author for the table mybbs (authorid). The authorid In the table mybbs is subject to the SQL server primary key id constraint in the table author:
Begin transaction
Alter table dbo. mybbs add constraint fk_mybbs_author
Foreign key (authorid)
References dbo. author ([id]) on update cascade on delete cascade

-- Delete the sl foreign key constraint fk_mybbs_author:
-- Alter table dbo. mybbs drop constraint fk_mybbs_author
-- Rollback
Commit transaction

The options on update cascade and on delete cascade indicate that the id field of the author table will be deleted in the future. During the update operation, IDs in the mybbs table will also be deleted or updated in cascade mode. If not selected, you cannot update or delete the IDS associated with the mybbs table in the author table.

View instances

Create table 'board '(

'Board _ id' int (11) not null auto_increment,

'Catalog _ id' int (11) not null default '0 ',

'Board _ name' varchar (100) character set utf8 not null default '',

'Board _ descrip 'varchar (255) character set utf8 default null,

'Board _ type' varchar (30) character set utf8 default null,

Primary key ('board _ id '),

Key 'foreign key' ('catalog _ id '),

Constraint 'foreign key constraint 'eign key ('catalog _ id') references 'catalog' ('catalog _ id') on delete cascade on update cascade // key statement for cascading Deletion

) Engine = innodb auto_increment = 13 default charset = gbk;


SQL Foreign keys:
1/, the most common one: reduce repeated data. table a has a foreign key, and table B's data cannot be deleted. select the force relationship between insert and update.
2/, followed by adding a subordinate table. if table a deletes a record and table B also deletes an associated record, the primary key of Table a is the foreign key of Table B in the foreign key relationship. In fact, table B is the subordinate table of Table a (that is, Table a is the parent table). If you select to force the relationship between insert and update, if you insert data to table B, the corresponding record must already exist in table. When you select cascade deletion, deleting a record in Table a deletes a record in table B.


*/
?>

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.