CREATE TABLE test_category (
	CategoryCode 	varchar(32) NOT NULL, 
	PRIMARY KEY 	(CategoryCode)
)

CREATE TABLE test_group (
	GroupCode 	varchar(32) NOT NULL, 
	CategoryCode	varchar(32) NOT NULL, 
	PRIMARY KEY (CategoryCode, GroupCode)
)

CREATE TABLE test_subject (
	SubjectCode	varchar(32) NOT NULL,
	GroupCode 	varchar(32) NOT NULL, 
	CategoryCode	varchar(32) NOT NULL, 
	PRIMARY KEY (CategoryCode, GroupCode, SubjectCode)
)

alter TABLE test_group 
	add constraint FK_1 FOREIGN KEY (CategoryCode)
	REFERENCES test_category (CategoryCode)

alter TABLE test_subject 
	add constraint FK_2 FOREIGN KEY (CategoryCode, GroupCode)
	REFERENCES test_group (CategoryCode, GroupCode)

INSERT INTO test_category (CategoryCode) VALUES("category one")
INSERT INTO test_category (CategoryCode) VALUES("category two")

INSERT INTO test_group (CategoryCode, GroupCode) VALUES("category one", "group one")
INSERT INTO test_group (CategoryCode, GroupCode) VALUES("category one", "group two")

INSERT INTO test_subject (CategoryCode, GroupCode, SubjectCode) 
	VALUES ("category one", "group one", "subject one")
INSERT INTO test_subject (CategoryCode, GroupCode, SubjectCode) 
	VALUES ("category one", "group one", "subject two")
INSERT INTO test_subject (CategoryCode, GroupCode, SubjectCode) 
	VALUES ("category one", "group one", "subject three")

