I'm using sqlite on iOS, and created the following tables:
CREATE TABLE asset (
uuid char(36) PRIMARY KEY NOT NULL,
site char(36) NOT NULL,
asset_type char(36) NOT NULL,
enabled bool NOT NULL,
display_order integer NOT NULL,
barcode varchar(128) NOT NULL,
location varchar(512) NOT NULL,
FOREIGN KEY (site) REFERENCES site (uuid)
);
CREATE TABLE site (
uuid char(36) PRIMARY KEY NOT NULL,
enabled bool NOT NULL,
display_order integer NOT NULL,
site_number varchar(128) NOT NULL,
site_name varchar(128) NOT NULL,
street_address varchar(500) NOT NULL,
suburb varchar(128) NOT NULL,
postcode varchar(32) NOT NULL,
state varchar(32) NOT NULL,
site_safety_notes varchar(1024)
);
I inserted a row into Asset with a site referencing a row that doesn't exist in the site table (there are no rows yet in this table) and sqlite happily went ahead and inserted the row.
According to docs, the way I've created the 'Asset' table, sqlite should enforce referential integrity, but it didn't. How can I have sqlite enforce referential integrity.
Aucun commentaire:
Enregistrer un commentaire