SET NOCOUNT ON
GO
DROP TABLE ADDRESS
GO
DROP TABLE CUSTOMER
GO
CREATE TABLE CUSTOMER
(
	cust_Code	varchar(10) NOT NULL,
	cust_Name	varchar(30) NOT NULL,
	PRIMARY KEY
	(
		cust_Code
	)
)
GO
CREATE TABLE ADDRESS
(
	adr_cust_Code	varchar(10) NOT NULL,
	adr_ID		int identity(1,1) NOT NULL,
	adr_Address	varchar(1000) NOT NULL,
	PRIMARY KEY
	(
		adr_cust_Code,
		adr_ID
	)
)
GO
INSERT INTO CUSTOMER
SELECT 'SMIT01', 'Smith'
GO
INSERT INTO ADDRESS
SELECT 'SMIT01', 'Smith Address Here'
GO
ALTER TABLE ADDRESS ADD CONSTRAINT
	FK_ADDRESS_CUSTOMER FOREIGN KEY
	(
		adr_cust_Code
	) REFERENCES CUSTOMER
	(
		cust_Code
	)
GO
PRINT ''
PRINT 'Attempt to update Customer without Address'
PRINT ''
GO
-- This is not allows!
UPDATE	U
SET	cust_Code = 'JONE01'
FROM	CUSTOMER U
WHERE	cust_Code = 'SMIT01'
GO
PRINT ''
PRINT 'Try with transaction block'
PRINT ''
-- This won't work either
BEGIN TRANSACTION
UPDATE	U
SET	cust_Code = 'JONE01'
FROM	CUSTOMER U
WHERE	cust_Code = 'SMIT01'
UPDATE	U
SET	adr_cust_Code = 'JONE01'
FROM	ADDRESS U
WHERE	adr_cust_Code = 'SMIT01'
COMMIT
GO
PRINT ''
PRINT '------------------------------------------'
PRINT ''
GO
PRINT 'Remove the CHECK on the FK'
PRINT ''
GO
ALTER TABLE ADDRESS
	NOCHECK CONSTRAINT FK_ADDRESS_CUSTOMER
GO
-- This will work, but allows orphans to be created
UPDATE	U
SET	cust_Code = 'JONE01'
FROM	CUSTOMER U
WHERE	cust_Code = 'SMIT01'
GO
PRINT ''
PRINT 'Data now has orphan address'
PRINT ''
GO
SELECT	* FROM CUSTOMER
SELECT	* FROM ADDRESS
GO
-- Restore the original data
UPDATE	U
SET	cust_Code = 'SMIT01'
FROM	CUSTOMER U
WHERE	cust_Code = 'JONE01'
GO
PRINT ''
PRINT 'Create Cascade FK'
PRINT ''
GO
ALTER TABLE ADDRESS
	DROP CONSTRAINT FK_ADDRESS_CUSTOMER
GO
ALTER TABLE ADDRESS WITH NOCHECK ADD CONSTRAINT
	FK_ADDRESS_CUSTOMER FOREIGN KEY
	(
		adr_cust_Code
	) REFERENCES CUSTOMER
	(
		cust_Code
	) ON UPDATE CASCADE
	 ON DELETE CASCADE
	
GO
PRINT ''
PRINT ''
PRINT '"Before" Data [Cascade is active]'
PRINT ''
SELECT	* FROM CUSTOMER
SELECT	* FROM ADDRESS
GO
UPDATE	U
SET	cust_Code = 'JONE01'
FROM	CUSTOMER U
WHERE	cust_Code = 'SMIT01'
GO
PRINT ''
PRINT '"After" Data'
PRINT ''
SELECT	* FROM CUSTOMER
SELECT	* FROM ADDRESS
GO
PRINT ''
PRINT 'Delete customer [Cascade is active]'
PRINT ''
DELETE	D
FROM	CUSTOMER D
WHERE	cust_Code = 'JONE01'
GO
SELECT	* FROM CUSTOMER
SELECT	* FROM ADDRESS
GO
SET NOCOUNT OFF
GO