Mega Code Archive

 

Categories / MSSQL Tutorial / Constraints

 

ON UPDATE CASCADE ON DELETE NO ACTION

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>