constraints.sql

constraints


  DROP TABLE CT

  -- Create table
CREATE TABLE CT (
    EID  INT CONSTRAINT PK_EID PRIMARY KEY,
    ENAME VARCHAR(30) NOT NULL,
    ELOC CHAR(3) CONSTRAINT DF_ELOC DEFAULT 'HYD',
    SAL  DECIMAL(8,2) CONSTRAINT CH_SAL CHECK (SAL > 10000),
    DID  INT REFERENCES MT(DID) -- FOREIGNKEY REFERENCES
);


--VS


CREATE TABLE CT (
    EID  INT CONSTRAINT PK_EID PRIMARY KEY,
    ENAME VARCHAR(30) NOT NULL,
    ELOC CHAR(3) CONSTRAINT DF_ELOC DEFAULT 'HYD',
    SAL  DECIMAL(8,2) CONSTRAINT CH_SAL CHECK (SAL > 10000),
    DID  INT -- WITHOUT FOREIGN KEY REFERENCE
);
-- Insert data
INSERT INTO CT (EID, ENAME, ELOC, SAL, DID)
VALUES (1, 'SREE', 'BAN', 40000, 200);   --DOES NOT ALLOW TO INSERT DID THAT ARE NOT THERE IN MT--->PARENT TABLE

INSERT INTO CT (EID, ENAME, ELOC, SAL, DID)
VALUES (2, 'DEVI', 'CHE', 50000, 10);

-- Select data
SELECT * FROM CT;


-- FOREIGN KEY
-- To make the relation between multiple tables
-- to maintain valid data across tables

CREATE TABLE MT (
    DID   INT CONSTRAINT PK_DID PRIMARY KEY,
    DNAME VARCHAR(30),
    MGRID INT
);

INSERT INTO MT (DID, DNAME, MGRID)
VALUES
    (10, 'SQL SERVER', 100),
    (20, 'ORACLE', 200),
    (30, 'MY SQL', 300),
    (40, 'MANGO DB', 400);


SELECT * FROM CT;
SELECT * FROM MT;