SQL Server SQL Query For INT Column(identity) to restrict values between 1 to 100

nzkunb0c 于 2023-05-16 发布在其他

关注(0)|答案(3)|浏览(159)

Suppose we have class with a 100 student limit, we make a column StudentId Column between (1-100) beyond this limit StudentId is not generated

Create Table Class
(
StudentId Int Primary Key Identity(1,1)
StudentName Varchar(25) 
) 

insert into Class values('Jhon')
/* 2 ..
..
..
To 100 (Column) */
insert into Class values('Joy')

Record 101

insert into Class values('Joy') --- When We insert 101 row a error will occur

sql-server

来源：https://stackoverflow.com/questions/29762766/sql-query-for-int-columnidentity-to-restrict-values-between-1-to-100

3条答案

按热度按时间

knsnq2tg1#

CREATE TABLE RegTable
(StudentId NUMBER(8,0),
CONSTRAINT CheckRegNumber  CHECK (StudentId <=100 and StudentId >0 )
);

Add a CONSTRAINT to your int column

赞(0）回复(0）举报 2023-05-16

gc0ot86w2#

CREATE TRIGGER LimitCount
For INSERT On Student
AS
    If (SELECT COUNT(StudentId) From STUDENT) > 100
       --DO SOMETHING OR ROLLBACK

One draw back about this is it doesn't guarantee, if the row are actually 100 if there is deletion. So you will have to do more in DO SOMETHING section

赞(0）回复(0）举报 2023-05-16

ncecgwcz3#

If you want to limit your ClassTable with 100 rows, you could create an after insert trigger. Since your column is identity column, you cannot rely on Id, because, you could be in a situation where you can't insert rows but you have less than 100 students. This usually occurs when you insert and delete rows. One way to solve this problem is by resetting identity column with DBCC CHECKIDENT command, which you do not want to be doing every time.

CREATE TRIGGER LimitRows
 on ClassTable
 after insert
 as
    declare @rowsCount int
    select @rowsCount = Count(*) from ClassTable
    if @rowsCount > 100
    begin
      rollback
    end

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server SQL Query For INT Column(identity) to restrict values between 1 to 100

3条答案

相关问题

热门标签

最新问答