Prevent Insert Or Update data in C# or SQL

Question

I have two table Header and Item, that has one to many Relation.

Header Table

Id      StartDate   EndDate 
--------------------------------------- 
1       1999/1/1    1999/5/1        
2       2000/1/1    2000/4/1
3       2000/1/1    2000/5/1

and Item Table

Id      HeaderRef   SLRef
-------------------------------------
101     1           201
102     2           201

How to Prevent to add Item with HeaderRef=3 and SLRef=201 because it has same SLRef, and the Header rows that HeaderRef referd to it has StartDate and EndDate that another Item with same SLRef Refered to Header in that Range.

Do you want to keep headerRef and SLRef unique in ItemTable? — Yawar Murtaza
– Yawar Murtaza, Commented Mar 13, 2017 at 9:54
Yes I want to keep headerRef and SLRef unique in Date range that added to Hedar. — Elham Azadfar
– Elham Azadfar, Commented Mar 13, 2017 at 10:00
something like this : isValid = !(Headers.Any(x => (Header.StartDate <= x.EndDate && DocHeader.EndDate >= x.StartDate))); — Elham Azadfar
– Elham Azadfar, Commented Mar 13, 2017 at 10:26

Yawar Murtaza · Accepted Answer · 2017-03-13 14:17:04Z

Presuming you are using MS SQL Server there are two approaches to achive what you are looking for:

1) Use trigger as suggested by other users. Trigger would be for INSERT / UPDATE that will check the date ranges and will allow new values to be added or raise error.

2) You can use composite primary key in ItemTable:

CREATE TABLE [dbo].[ItemTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [HeaderRef] [int] NOT NULL,
    [SLRef] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [HeaderRef] ASC,
    [SLRef] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now this will put a constraint on the ItemTable and sql server WILL NOT allow duplicate combination of headerRef and SLRef int values (keys).

Back you your HeaderTable, you can put the unique constraint to stop duplicating the range of start and end dates

CREATE TABLE [dbo].[HeaderTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [STARTDATE] [datetime] not NULL,
    [ENDDATE] [datetime] not NULL,
 CONSTRAINT [PK_HeaderTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now create unique index on HeaderTable for start and end dates.

/****** Object:  Index [IX_HeaderTable]    Script Date: 03/13/2017 12:24:51 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_HeaderTable] ON [dbo].[HeaderTable] 
(
    [ENDDATE] ASC,
    [STARTDATE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

You can also put another constraint on HeaderTable that will check if start date cant be before end date.

ALTER TABLE [dbo].[HeaderTable]  WITH CHECK ADD  CONSTRAINT [CheckEndLaterThanStart] CHECK  (([ENDDATE]>=[STARTDATE]))
GO

ALTER TABLE [dbo].[HeaderTable] CHECK CONSTRAINT [CheckEndLaterThanStart]
GO

Hope this helps!

Mostafa Mohamed Ahmed · Accepted Answer · 2017-03-13 10:54:22Z

1

You Are Looking For a DML Trigger , in simple terms it's some sort of a sql function or procedure that is called automatically when the user try to change the data inside a database by adding or removing , Also the body of the trigger can contain some validation logic so it won't insert the data unless it meets a certain condition

answered Mar 13, 2017 at 10:54

Mostafa Mohamed Ahmed

6693 silver badges12 bronze badges

Collectives™ on Stack Overflow

Prevent Insert Or Update data in C# or SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related