ON UPDATE CASCADE ON DELETE NO ACTION : REFERENCES « Constraints « SQL Server / T-SQL Tutorial






2> CREATE TABLE customer
3> (
4> cust_id      int            NOT NULL  IDENTITY  PRIMARY KEY,
5> cust_name    varchar(50)    NOT NULL
6> )
7> GO
1> CREATE TABLE orders
2> (
3> order_id    int        NOT NULL  IDENTITY  PRIMARY KEY,
4> cust_id     int        NOT NULL  REFERENCES customer(cust_id)
5>                        ON UPDATE CASCADE ON DELETE NO ACTION
6> )
7> GO
1>
2> EXEC sp_helpconstraint customer
3> GO
Object Name



--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
customer




constraint_type                                                                                                                                    constraint_name
                                                                            delete_action update_action status_enabled status_for_replication constraint_keys











-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
PRIMARY KEY (clustered)                                                                                                                            PK__customer__43A1D464
                                                                            (n/a)         (n/a)         (n/a)          (n/a)                  cust_id












Table is referenced by foreign key


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
master.dbo.orders: FK__orders__cust_id__4E1F62D7


1>
2> EXEC sp_helpconstraint orders
3> GO
Object Name



--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
orders




constraint_type                                                                                                                                    constraint_name
                                                                            delete_action update_action status_enabled status_for_replication constraint_keys











-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
FOREIGN KEY                                                                                                                                        FK__orders__cust_id__4E1F62D7
                                                                            No Action     Cascade       Enabled        Is_For_Replication     cust_id












                                                                                                                                              REFERENCES master.dbo.customer (cust_id)











PRIMARY KEY (clustered)                                                                                                                            PK__orders__4D2B3E9E
                                                                            (n/a)         (n/a)         (n/a)          (n/a)                  order_id












No foreign keys reference table 'orders', or you do not have permissions on referencing tables.
1>
2> drop table orders;
3> GO
1>
2>








7.4.REFERENCES
7.4.1.Self-Referencing Tables
7.4.2.Primary key and foreign key
7.4.3.Making a Table be Self-Referencing
7.4.4.ON UPDATE CASCADE ON DELETE NO ACTION
7.4.5.REFERENCES customer(cust_id) (from another table)