/* 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
