kg-construct / rml-test-cases

RML conformance test suite
http://rml.io/test-cases/
Creative Commons Attribution 4.0 International
4 stars 12 forks source link

RMLTC0009c: reorder DROP TABLE statements for SQL resources for test repeatability #49

Open mcndt opened 3 years ago

mcndt commented 3 years ago

Currently, the test case RMLTC0009c drops a referenced table before dropping the table that registered the foreign key. When running this test twice in a row, this can result in an a foreign key exception in some RDBs.

I propose to reorder the SQL script such that this conflict cannot occur:

DROP TABLE IF EXISTS test.Student;  -- reordered
DROP TABLE IF EXISTS test.Sport;    -- reordered

CREATE TABLE Sport (
ID integer,
Name varchar (50),
PRIMARY KEY (ID)
);

CREATE TABLE Student (
ID integer,
Name varchar(50),
Sport integer,
PRIMARY KEY (ID),
FOREIGN KEY(Sport) REFERENCES Sport(ID)
);

INSERT INTO Sport (ID, Name) VALUES (100,'Tennis');
INSERT INTO Student (ID, Name, Sport) VALUES (10,'Venus Williams', 100);
INSERT INTO Student (ID, Name, Sport) VALUES (20,'Demi Moore', NULL);