Tag: Constraint

USE Arm_DB
Go

 IF (OBJECT_ID('TBL_Product','U') IS NOT NULL)
   DROP TABLE TBL_Product

Go

CREATE TABLE TBL_Product
(
ID INT IDENTITY(1,1)
,NAME NVARCHAR(50)
,Price DECIMAL(10,2)
,[Desc] NVARCHAR(MAX)
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,MOdifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TBL_Product PRIMARY KEY (ID)
)

GO 

 IF (OBJECT_ID('TBL_Sales','U')IS NOT NULL )
    DROP TABLE TBL_Sales

CREATE TABLE TBL_Sales
(
ID INT IDENTITY(1,1)
,ProductID INT 
,Quantity INT
,SalesDate DATETIME
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,ModifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TB_Sales PRIMARY KEY (ID)
CONSTRAINT FK_TBL_Sales_TBL_Product 
     FOREIGN KEY (ProductID) REFERENCES dbo.TBL_Product(ID)
)

GO

INSERT dbo.TBL_Product   ( NAME ,Price )
SELECT 'Book',32.4
UNION ALL
SELECT 'Pen',5.2
UNION ALL
SELECT 'Notebook',8.7

Go

INSERT INTO dbo.TBL_Sales( ProductID , Quantity ,SalesDate)
SELECT 1,4,'2013-Apr-10'
UNION ALL
SELECT 1,3,'2013-Apr-11'
UNION ALL
SELECT 1,8,'2013-Apr-13'
UNION ALL
SELECT 1,1,'2013-Apr-18'
UNION ALL
SELECT 2,4,'2013-Apr-10'
UNION ALL
SELECT 2,3,'2013-Apr-11'
UNION ALL
SELECT 2,8,'2013-Apr-13'
UNION ALL
SELECT 2,1,'2013-Apr-18'
UNION ALL
SELECT 3,4,'2013-Apr-10'
UNION ALL
SELECT 3,5,'2013-Apr-11'
UNION ALL
SELECT 3,3,'2013-Apr-13'
UNION ALL
SELECT 3,9,'2013-Apr-18'

SELECT * FROM dbo.TBL_Product

SELECT * FROM dbo.TBL_Sales