Implementing Tables and Views Demo

CREATE DATABASE demodb
GO
USE demodb
GO

-- Create two tables and populate them

CREATE TABLE dbo.Mountain
(
    MountainID INT IDENTITY(1,1) PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    Lenght INT NOT NULL,
    Country nvarchar(50)
);
GO

INSERT INTO Mountain(Name, Lenght, Country)
VALUES ('Qgo Q', '66', 'Qgovica'),
 ('Everest', '889', 'Bulgaria'),
 ('Monte Negro', '5443', 'Na majnata si');
 GO

 DROP TABLE dbo.Peak
 GO

 CREATE TABLE dbo.Peak
 (
    PeakID INT IDENTITY(1000,1) PRIMARY KEY,
    MountainID INT NOT NULL
    FOREIGN KEY REFERENCES dbo.Mountain(MountainID),
    PeakName nvarchar(50) NOT NULL,
    SomePrice DECIMAL(18,2) NOT NULL
 );
 GO

 INSERT INTO Peak(MountainID, PeakName, SomePrice)
 VALUES (2, 'Halifaks', 12.34),
 (1, 'DjaBudja', 11.01),
 (1, 'EngiBengi', 34.44),
 (3, 'HakunaMatata', 69.69);
 GO

 -- Try to insert ivalid data and note how poor is the error
 -- message


 INSERT INTO Peak(MountainID, PeakName, SomePrice)
 VALUES (7, 'Halifaks', 12.34)
 GO

 -- Try to remove data
 DELETE FROM dbo.Mountain WHERE MountainID=1;
 GO

 -- To be able to do that we need to remove the constraint and replace
 -- it with named constraint with cascade delete option

 ALTER TABLE dbo.Peak
    DROP CONSTRAINT FK__Peak__MountainID__22AA2996;
GO

ALTER TABLE dbo.Peak
    ADD CONSTRAINT FK_Peak_Mountain
    FOREIGN KEY (MountainID)
    REFERENCES dbo.Mountain (MountainID)
    ON DELETE CASCADE;
GO


-- Note how cascade option caused references to be deleted too
DELETE FROM dbo.Mountain WHERE MountainID = 1;
GO
SELECT * FROM Peak