KR-2

CREATE TABLE Doctor(
    id      int identity(1,1) NOT NULL,
    dep_id  int NOT NULL,

    fio     varchar(50) NOT NULL CHECK(fio !=' '),
    license int UNIQUE  NOT NULL CHECK(license > 99999 AND license < 1000000)

    PRIMARY KEY(id)
);

CREATE TABLE Department(
    id      int identity(1,1) NOT NULL,

    name    varchar(50) NOT NULL CHECK(name !=' '),

    PRIMARY KEY(id)
);

CREATE TABLE Ward(
    id      int identity(1,1) NOT NULL,
    dep_id  int NOT NULL,
    num     int NOT NULL,

    -- Альтернативный Ключ
    CONSTRAINT ak_ward Unique (dep_id, num),
    PRIMARY KEY(id)
);

CREATE TABLE Paciente(
    id      int identity(1,1) NOT NULL,

    name    varchar(50) NOT NULL CHECK(name !=' '),

    PRIMARY KEY(id)
);

CREATE TABLE DocPoc(
    doc_id      int NOT NULL,
    poc_id      int NOT NULL,

    PRIMARY KEY CLUSTERED(doc_id, poc_id),
);

ALTER TABLE Doctor ADD FOREIGN KEY (dep_id) REFERENCES Department(id);
ALTER TABLE Ward   ADD FOREIGN KEY (dep_id) REFERENCES Department(id);

--ALTER TABLE Ward ADD PRIMARY KEY (id);