Primary key and foreign key : REFERENCES « Constraints « SQL Server / T-SQL Tutorial






4> CREATE TABLE master_customer
5> (
6> cust_id      int            NOT NULL  IDENTITY  PRIMARY KEY,
7> cust_name    varchar(50)    NOT NULL
8> )
9> GO
1>
2>
3> CREATE TABLE customer_location
4> (
5> cust_id     int            NOT NULL,
6> cust_loc    smallint       NOT NULL,
7> CONSTRAINT PK_CUSTOMER_LOCATION PRIMARY KEY (cust_id,cust_loc),
8> CONSTRAINT FK_CUSTOMER_LOCATION FOREIGN KEY (cust_id)
9>     REFERENCES master_customer (cust_id)
10> )
11> GO
1>
2>
3> CREATE TABLE orders
4> (
5> order_id    int            NOT NULL  IDENTITY  PRIMARY KEY,
6>
7> cust_id     int            NOT NULL,
8> cust_loc    smallint       NOT NULL,
9> CONSTRAINT FK_ORDER_MASTER_CUST FOREIGN KEY (cust_id)
10>     REFERENCES master_customer (cust_id),
11> CONSTRAINT FK_ORDER_CUST_LOC FOREIGN KEY (cust_id, cust_loc)
12>     REFERENCES customer_location (cust_id, cust_loc)
13> )
14> GO
1>
2> drop table orders;
3> GO
1>
2> drop table customer_location;
3> GO
1>
2> drop table master_customer;
3> GO








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)