SQLize | PHPize | SQLtest

CREATE TABLE torgtchk (tchk_id SMALLINT NOT NULL PRIMARY KEY, etaj INT, square INT, condicioner VARCHAR(5)); CREATE TABLE preyscurant (preyscurnum SMALLINT NOT NULL PRIMARY KEY, stoimost INT, data_s DATETIME, data_po DATETIME, tchk_id SMALLINT NOT NULL REFERENCES torgtchk); CREATE TABLE vid( vid_kod SMALLINT NOT NULL PRIMARY KEY, vid_lica VARCHAR(20)); CREATE TABLE client( client_id SMALLINT NOT NULL PRIMARY KEY, FIO VARCHAR(65), adres VARCHAR(65), telephone VARCHAR(20), vid_kod SMALLINT NOT NULL REFERENCES vid); CREATE TABLE dogovor( dogovor_id SMALLINT NOT NULL PRIMARY KEY, tchk_id SMALLINT NOT NULL REFERENCES torgtchk, data_nach DATETIME, data_konc DATETIME, preyscurnum SMALLINT NOT NULL REFERENCES preyscurant, client_id SMALLINT NOT NULL REFERENCES client ); ALTER TABLE torgtchk ADD CONSTRAINT chk_condicioner CHECK (condicioner IN ('есть', 'нет')); ALTER TABLE torgtchk ADD DEFAULT 'есть' FOR condicioner; ALTER TABLE vid ADD CONSTRAINT chk_vid CHECK (vid_lica IN ('юридическое', 'физическое')); ALTER TABLE vid ADD DEFAULT 'юридическое' FOR vid_lica; SELECT name AS foreign_key_name FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dogovor') AND referenced_object_id = OBJECT_ID('torgtchk'); ALTER TABLE dogovor DROP CONSTRAINT FK__dogovor__tchk_id__29221CFB; ALTER TABLE dogovor DROP COLUMN tchk_id; CREATE TABLE dogovor_torgtchk ( dogovor_id SMALLINT NOT NULL REFERENCES dogovor(dogovor_id), tchk_id SMALLINT NOT NULL REFERENCES torgtchk(tchk_id), PRIMARY KEY (dogovor_id, tchk_id) -- Уникальная пара "договор + помещение" ); INSERT INTO vid (vid_kod, vid_lica) VALUES (1, 'юридическое'), (2, 'физическое'); SELECT * FROM vid; -- SELECT * FROM person; -- SELECT * FROM purchase; -- SELECT * FROM product; -- SELECT * FROM old_item; -- SELECT * FROM purchase_archive;