/* This script creates two tables with a composite
** Primary Key/Foreign Key relationship. It then displays the
** constraints on each table.
*/

if exists (select * from sysobjects where name='customer' and type='U')
        DROP TABLE customer
GO

if exists (select * from sysobjects where name='orders' and type='U')
        DROP TABLE orders
GO

CREATE TABLE customer
(
cust_id int NOT NULL,
location_num smallint NULL,
cust_name varchar(50) NOT NULL,
CONSTRAINT CUSTOMER_UNQ UNIQUE CLUSTERED (location_num, cust_id)
)

CREATE TABLE orders
(
order_id int NOT NULL IDENTITY CONSTRAINT ORDER_PK
PRIMARY KEY NONCLUSTERED,
cust_num int NOT NULL,
cust_loc smallint NULL,
CONSTRAINT FK_ORDER_CUSTOMER FOREIGN KEY (cust_loc, cust_num)
REFERENCES customer (location_num, cust_id)
)

GO

EXEC sp_helpconstraint customer
EXEC sp_helpconstraint orders
GO