CHECK Constraint To Prevent A Conditional Duplicate
Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-Paul
View Complete Forum Thread with Replies
Related Forum Messages:
What Type Of Constraint To Prevent Duplicates
I am trying to either write a trigger or a check constraint to preventduplicates in my table. There are two columns I need to look at for theduplicates and only one combo value for both columns is allowed in thetable. For e.g.Column Serial can have only one '123456' value with testresult value as'PASS'. This serial can be in the table many times with any other comboso for e.g.The table could contain 100 entries for serial column value '123456'with testresult value 'FAIL', 'PENDING' etc.** TESTED SCHEMA BELOW **-- create tableCREATE TABLE bstresult(ID int IDENTITY (1, 1) NOT NULL ,serial char (10) NULL ,testresult char (10) NULL)-- Insert valid valuesinsert into bstresult values ('123456','PASS')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','PENDING')-- insert invalid value this should failinsert into bstresult values ('123456','PASS')If I simply create a unique constraint on both columns it will notallow the FAIL combo or PENDING combo with the same serial which I needto allow.Appreciate your help.
View Replies !
CHECK CONSTRAINT And WITH CHECK Option: SQL 2000 Vs SQL 2005
Hi I have got this script and I was comparing the execution plan query at the end on both SQL 2000 and SQL 2005. IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VW_PTN') DROP VIEW VW_PTN GO IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TB_PTN_1') DROP TABLE TB_PTN_1 GO -- CREATE PARTITION TABLE 1 create table [dbo].[TB_PTN_1] ( [PTN_ID] int IDENTITY (0, 1) NOT FOR REPLICATION NOT NULL , [DATE_TIME] DATETIME NOT NULL, [PTN_NUMBER] int NOT NULL ) ON [PRIMARY] GO -- ADD PRIMARY KEY ALTER TABLE [dbo].[TB_PTN_1] WITH CHECK ADD CONSTRAINT [PK_TB_PTN_1] PRIMARY KEY CLUSTERED ([PTN_ID], [PTN_NUMBER]) ON [PRIMARY] GO -- ADD CHECK CONSTRAINT ON PTN_NUMBER ALTER TABLE [dbo].[TB_PTN_1] WITH CHECK ADD CONSTRAINT [CK_TB_PTN_1] CHECK (PTN_NUMBER=1) GO IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TB_PTN_2') DROP TABLE TB_PTN_2 GO -- CREATE PARTITION TABLE 2 create table [dbo].[TB_PTN_2] ( [PTN_ID] int IDENTITY (0, 1) NOT FOR REPLICATION NOT NULL , [DATE_TIME] DATETIME NOT NULL, [PTN_NUMBER] int NOT NULL ) ON [PRIMARY] GO -- ADD PRIMARY KEY ALTER TABLE [dbo].[TB_PTN_2] WITH CHECK ADD CONSTRAINT [PK_TB_PTN_2] PRIMARY KEY CLUSTERED ([PTN_ID], [PTN_NUMBER]) ON [PRIMARY] GO -- ADD CHECK CONSTRAINT ON PTN_NUMBER ALTER TABLE [dbo].[TB_PTN_2] WITH CHECK ADD CONSTRAINT [CK_TB_PTN_2] CHECK (PTN_NUMBER=2) GO IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'VW_PTN') DROP VIEW VW_PTN GO -- CREATE PARTITIONED VIEW CREATE VIEW VW_PTN AS SELECT * FROM TB_PTN_1 UNION ALL SELECT * FROM TB_PTN_2 GO set showplan_text off go set showplan_all on go SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2 go set showplan_all off go This is what I see in SQL server 2000 SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2 |--Concatenation |--Filter(WHERESTARTUP EXPR(Convert([@1])=1))) | |--Clustered Index Scan(OBJECT[Testerdatabase].[dbo].[TB_PTN_1].[PK_TB_PTN_1]), WHERE[TB_PTN_1].[PTN_NUMBER]=Convert([@1]))) |--Filter(WHERESTARTUP EXPR(Convert([@1])=2))) |--Clustered Index Scan(OBJECT[Testerdatabase].[dbo].[TB_PTN_2].[PK_TB_PTN_2]), WHERE[TB_PTN_2].[PTN_NUMBER]=Convert([@1]))) This is what I see in SQL server 2005 SELECT * FROM VW_PTN WHERE PTN_NUMBER = 2 |--Compute Scalar(DEFINE[TestDatabase].[dbo].[TB_PTN_2].[PTN_ID]=[TestDatabase].[dbo].[TB_PTN_2].[PTN_ID], [TestDatabase].[dbo].[TB_PTN_2].[DATE_TIME]=[TestDatabase].[dbo].[TB_PTN_2].[DATE_TIME], [TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER]=[TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER])) |--Clustered Index Scan(OBJECT[TestDatabase].[dbo].[TB_PTN_2].[PK_TB_PTN_2]), WHERE[TestDatabase].[dbo].[TB_PTN_2].[PTN_NUMBER]=(2))) Whats the difference? Why does it scan both the tables in SQL server 2000 and just one table in SQL server 2005? It seems to be that SQL server 2000 is ignoring the WITH CHECK option whilst creating the check constraint on each of the tables for PTN_NUMBER column. Any clues?
View Replies !
How Can I Prevent From Inserting Duplicate Data?
I have a table storing only 2 FKs, let's say PID, MID Is there any way that I can check distinct data before row is added to this table? For example, current data is PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449 If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue? Thanks.
View Replies !
Prevent Duplicate Entries In A Table
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember. The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID. Here is a sample of some data: User1 Group1 User1 Group2 User2 Group2 User3 Group1 User3 Group3 Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example: User1 Group1 User2 Group2 User1 Group1 I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome. Is there a simpler way to prevent duplicate entries in a table using sql? Thanks a lot, Chris
View Replies !
Conditional Insert - Duplicate Key Issue
I am in the process of migrating an old access database into a new SQL Server 2000 database with a modified structure. Tables: [Chapters][Worksite][Employers] I want to insert into the chapters table a ChapterID(PK), EmployerID(FK), and ChapterName. All of the selected information is coming from the [Worksite] table. However, my conditional is based on a third table, the [Employers] table. Below is a copy of a query that returns the result set I would like to insert into my [Chapters] table. Code: Select distinct a.WorksiteCode, b.employerID, a.WorksiteName From Worksite a, employers b Where a.worksiteCode = b.employerWSCode When I run this query it appears to return the proper result set. However, when I turn that query into an insert statement: Code: SET IDENTITY_INSERT Chapters ON Insert into Chapters (chapterID, employerID, chapterName) Select distinct a.WorksiteCode, b.employerID, a.WorksiteName From Worksite a, employers b Where a.worksiteCode = b.employerWSCode SET IDENTITY_INSERT Chapters OFF I recieve the following error message: Server: Msg 2627, Level 14, State 1, Procedure InsertChapterFromFixed, Line 10 Violation of PRIMARY KEY constraint 'PK_Chapters'. Cannot insert duplicate key in object 'Chapters'. The statement has been terminated. Anyone have any idea on how I can avoid this error message... either my distinct statement is not function as I expect it to, or I am doing something else wrong. Thanks for any help.
View Replies !
SSIS- Doing Conditional Duplicate Removals
Hi, First post here. Anyway, I have a question regarding SSIS. I'm currently given a task that requires reading a flat file, applying duplicate removal as well as invalid data removal, processing it, and finally writing it to a SQL Server 2005 DB. Part of the processing requires checking for partial duplicates in the batches of records provided in the text file. For example, the record contains a a phone number, status, timestamp of creation and various other entries. If a phone number is repeated (meaning, duplicate entry), a column called 'Status' must be checked, and only entries with the status of 'C' is allowed through. Another part of the processing requires that if the phone number is repeated along with various other entries including status, the timestamp of creation is checked and only the entry with the most recent timestamp is accepted. I would like to know how to implement this in SSIS without using table objects and scripts, as my experience tells me that doing this in a script can really take a hit on system performance. The task is expected to handle tens of thousands of records in a day. Any help will be appriciated. Thanks.
View Replies !
Check Constraint
Hi I was wodering how to add an OR statment right in the Check Constraint expression. This is what I am starting with in the database ([zip] like '[0-9][0-9][0-9][0-9][0-9]') and what I want well not exact but this would answer my question ([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]') Thanks for any help
View Replies !
Check Constraint?
In SQL Server 2000, I want to apply a check constraint on a column type varchar that it is not duplicated. Could someone help me out with this? Also, should I just make this column the primary key instead of an identity field being the primary key? What is the performance difference by applying the constraint rather then just making it the primary key? Mike B
View Replies !
Check Constraint
I have an existing table with field ZIPCODE defined as VARCHAR(5). I want to add a check constraint to allow only number from 0 to 9. This is what I did but it gave me error: alter table test with check add constraint ck_test check (zip between '0' and '9') error: ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'. The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'. What did I do wrong. Thanks for your help.
View Replies !
Check Constraint
hi, I want to implement a constraint on a talbe for two fields phone numbers should b (###)###-#### and ss# should be ###-##-#### How can I create such constraint. I tried, but got an error message and could not save the table with the new changes. Thanks Ahmed
View Replies !
Check Constraint
Hi, If I diseable a Foreign Key like this: Alter table table_name nocheck constraint constraint_name I'm able to insert inconsitent data, then when I restart the contraint like this: Alter table table_name check constraint constraint_name the constraint dont check the current integrity of the data, exist any way, without having to recreate the foreign key, to check the data integrity? Thanks
View Replies !
Check Constraint Help
Hi, i want to put a check constraint on one of my tables to make sure that a member doesn't get loaded into the same usergroup twice. Could anyone please help me with this. Here is some sample code: Code Block DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), MemberID INT, ClientID INT, UsergroupID INT) INSERT INTO @MyTable VALUES (123456, 211, 3054) -- Member gets loaded twice INSERT INTO @MyTable VALUES (123456, 211, 3054) INSERT INTO @MyTable VALUES (123456, 211, 3055) -- Same Member as the top but into ---- a different usergroup which is correct INSERT INTO @MyTable VALUES (215489, 376, 2017) INSERT INTO @MyTable VALUES (987512, 345, 2237) INSERT INTO @MyTable VALUES (235479, 333, 1158) INSERT INTO @MyTable VALUES (354985, 333, 1158) SELECT * FROM @MyTable The problem i have is that if a member is loaded into the same usergroup twice then the sites crash on the web applications and the member can't get into his profile page.Unfortunately i don't know which one of the 1000 stored procedures are used to access this table so that i could just change the query to SELECT TOP 1. However i think that it will be better practive to just put a check contraint on the table and avoide the whole problem. However 1 member can belong to multiple usergroups. Any help will be greatly appreciated. Kind Regards Carel Greaves
View Replies !
Check Constraint
In our database we have an indexed field that is using unique values, so in the index we turned on the unique property. Now with some changes we made in the application this value can be Null (could not be Null in the past). When we have more as 1 value having value Null we get an exception of unique key violation. Therefor we want to make an check constraint the checks if the value allready exists when the value is not Null. Is this possible and how can it be done?
View Replies !
Using Check Constraint
Hi, I am creating a table in which i want a column to contain only these characters..ie alphabets from a-z alphabets from A-Z numbers from 0-9 and allow characters @ _ underscore.. . - hyphen ie suppose i have table called login..and i have 2 columns in this table named Loginid LoginName... So LoginName shuld accept only the above characters.while inserting data into this table.. Kindly help
View Replies !
Check Constraint
declare @taxregcode varchar(100) declare @entitycd varchar(100) set @taxregcode='pntcircle' set @entitycd='NA' select * from trd_tax_group_hdr A (nolock), trd_tax_group_dtl B (nolock) where and tax_id=tg_taxid and isnull(tax_region,'ALL') = Isnull(@taxregcode,'ALL') and isnull(item_code,'NA')= isnull(@entitycd,'NA') and tax_type = 'SER' for the above query iam passing taxregcode,entitycd with value as assigned.first it is should check whether the passed data (@taxregcode) exist in a column tax_region(trd_tax_group_hdr table) if passed value not exist in table i should assign 'all' for both side.. the above query is not working..
View Replies !
Constraint/identity Which Allows Duplicate Null Fields
hi, I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005? Thank you.
View Replies !
Using Subquery In CHECK Constraint
Hi, I am trying to enforce a rule that only one record in a table can have status 'Active'. I'd like to do this with CHECK constraint rather than trigger. I know SQL server doesn't support subqueries such as : alter table add constraint chk_OnlyOneActive CHECK (1=(select count(*) from mytable where status='Active')) Is there any workaround for this? P.S. I tried using UDF like this: alter table add constraint chk_OnlyOneActive CHECK (dbo.NumActiveRecords()=1) and it works for insert - I am not able to insert a second "Active" record, but for some reason doesn't work for update - I am able to update existing record to status "Active" and end up with two or more Active records in the table. Thank you.
View Replies !
Check Constraint - SQL Problem
Hi,I am new to database development and am writing a database as part of auniversity courseI have created a table as below called CableWire - the table is created ok.CREATE TABLE CableWire(CableWireID CHAR(7),BSstandard CHAR(16),Colour VARCHAR(16),Material VARCHAR(16),MetresInStock INTEGER,PRIMARY KEY (CableWireID));However when I try to alter the table by adding a CHECK constraint:ALTER TABLE CableWireADD CHECK (MetresInStock >= 0);I get a pop-up box: "Line: 21SQLSTATE = 37000[Microsoft][ODBC dBase Driver] Syntax error in field definition, Continue?"(line 21 equated to the 2nd of those 2 lines). The syntax seems perfectlyacceptable to me. Any help appreciated.Regards,Mary
View Replies !
Parentheses In A Check Constraint
Can we use parentheses in a check constraint in MS-SQL-server DDL?e.g. I'm having a problem with the following statement:ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [CK_MyTable_TimesDataOK]CHECK (([TimeOn] IS NULL AND [TimeOff] IS NULL)OR([ShiftCode] IS NOT NULL AND [TimeOn] IS NOT NULL AND [TimeOff] IS NOTNULL));The statement appears to run fine, but when I look at my tabledefinition afterwards, it appears that SQL-server ignored theparentheses in my constraint; it shows the constraint expression as:(([TimeOn] IS NULL AND [TimeOff] IS NULL OR [ShiftCode] IS NOT NULL AND[TimeOn] IS NOT NULL AND [TimeOff] IS NOT NULL))My intention is that if there's (non null) data in either of the columnsTimeOn or TimeOff is not null, all three of the columns TimeOn, TimeOffand ShiftCode must have non null data.OK, I realise I could enforce this by altering my table setup in otherways. Right now I'm just trying to figure out if this I'm just upagainst a difference between dialects of SQL in check constraints here.Am I missing something obvious with parentheses?BTW the DDL for the table I'm testing on:CREATE TABLE [dbo].[MyTable]([FNname] [nvarchar](50) NOT NULL,[ShiftDate] [datetime] NOT NULL,[ShiftCode] [nchar](2) NULL,[TimeOn] [nchar](4) NULL,[TimeOff] [nchar](4) NULL);
View Replies !
? On Check Constraint Expressions
I am creating a check constraint on a field (GRID_NBR) for values between 1 & 99. I am a little confused on creating the expression for it (Books online is vague). Can I use the following expression: GRID_NBR BETWEEN 1 AND 99 Or do I have to use: GRID_NBR > 0 AND GRID_NBR < 100 Thanks!
View Replies !
Check Constraint Question
Hi I have created a table which contains date periods. It has 3 fields all with DateTime data types. These fields being ID, BeginDate, and EndDate. The ID field will hold a date indicating what month the reord is for, ie 01/03/2007 being March or 05/04/2007 being April. The BeginDate will contain the beginnig date for the financial month and EndDate for the financial month. I am trying to apply a check constraint on the ID field so that combaination of a month and year can only be entered once, ie. if 01/03/2007 already exists in the table then 03/03/2007 cannot. Is this possible? Thanking you in advance!
View Replies !
Having Issues! Using A UDF In A Check Constraint.
Hi there. I really hope somebody can point out what's going wrong here.. Firstly, what I'm trying to do is ensure that when a user adds a row to one of my tables, lets call it a WorkDateRange table, that the dates entered never overlap for the same work. This constraint needs to be created at the same time the Database is created. I'm using a function in the check constraint that is also created when the DB is. This table will store types of work as an Int along with a start date and an end date for the range. eg. 9999 2008/04/01 2008/04/22, that would be a work type of 9999 and a date range of 2008/04/01 - 2008/04/22. Therefore a user should not be able to enter this as the next row entry, 9999 2008/04/12 2008/04/30, without violating the check constraint. Now thats all good in theory but in practise I just can't get it! Here is the code I am using: ~first the DB is created~ <now the Function> CREATE FUNCTION WorkDateRange_CheckDateOverlapping ( @Work INT, @Date DATETIME ) RETURNS BIT AS BEGIN DECLARE @LogicalValue BIT, @StartDate DATETIME, @EndDate DATETIME DECLARE WorkDateRange_Cursor CURSOR FOR SELECT StartDateRange, EndDateRange FROM WorkDateRange WHERE Work = @Work SET @LogicalValue = 1 OPEN WorkDateRange_Cursor FETCH NEXT FROM WorkDateRange_Cursor INTO @StartDate, @EndDate WHILE @@FETCH_STATUS = 0 BEGIN SELECT @LogicalValue = CASE WHEN @Date BETWEEN @StartDate AND @EndDate THEN 0 ELSE 1 END IF @LogicalValue = 0 BREAK FETCH NEXT FROM WorkDateRange_Cursor INTO @StartDate, @EndDate END CLOSE WorkDateRange_Cursor DEALLOCATE WorkDateRange_Cursor RETURN @LogicalValue END GO <Then the table is created with these columns and these constraints on StartDateRange and EndDateRange> Columns: [UID] BIGINT, Work INT, StartDateRange DATETIME, EndDateRange DATETIME CONSTRAINT CHK_StartDateRange CHECK (dbo.WorkDateRange_CheckDateOverlapping(Work, StartDateRange) = 1) CONSTRAINT CHK_StartDateRange CHECK (dbo.WorkDateRange_CheckDateOverlapping(Work, EndDateRange) = 1) When I create the database and tables all is successful, but when I try enter rows into the WorkDateRange table, I get a message that my check constraint is violated. But if I copy my code into a separate query and declare the Work and Date variables and assign them the values that would be assigned during the check. I get the desired result of 1, which should be passing the check. Any help would be greatly appreciated Thanks in Advance.
View Replies !
'check Constraint' Ignored - PK Violation
I thought the whole point of 'check constraint' being unticked meant you could whack your data straight in no probs. How can I insert a bunch of historical information where I want to control the PK, in this case DealID? I have got 'keep identity' checked. I check the db, the Id does NOT exist currently. Error: 0xC0202009 at Data Cleansing, Deal Insert [12194]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_DealID'. Cannot insert duplicate key in object 'dbo.Deal'.".
View Replies !
Check Constraint To RAISEERROR On 547 16 1
Hi, I need the T-SQL statement on how to raise a custom error message for a check contraint @au_id '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9]' when ever a client enters the wrong SSN format such as 575-444865. I used the t-sql statement below but does not work when running an insert on the authors table ; au_id column. CREATE TRIGGER trg_datavalidate ON authors FOR INSERT AS DECLARE @au_id varchar(11) DECLARE @err INT SELECT @err = @@ERROR from inserted IF @err = 547 BEGIN PRINT 'Au_id needs to be in the SSN format xxx-xx-xxxx!. Error Number:' + CAST(@err AS VARCHAR) RAISERROR ('au_id must be in the format of xxx-xxxxxx where x is a number.', -- Message text. 16, -- Severity. 1 -- State. ); ROLLBACK TRANSACTION END
View Replies !
How Do I Write This Check Constraint?
Hi all, Any advice much appreciated. I have a table that defines the metadata of some values that will be stored in some generic table somewhere. Here's the table definition: CREATE TABLE [dbo].[AttributeType] ( [AttributeTypeID] [int] NOT NULL , [AttributeTypeName] [nvarchar] (40) NOT NULL , [AttributeDataType] [nvarchar] (40) NOT NULL CONSTRAINT CK_AttributeDataType_Values CHECK ([AttributeDataType] IN ('NVARCHAR','INT','DECIMAL','DATETIME')) , [AttributeLength] tinyint NULL , [AttributePrecision]tinyint NULL , [AttributeScale] tinyint NULL , CONSTRAINT CK_AttributeScale_Values CHECK (AttributeScale <= AttributePrecision) ) ON [PRIMARY] What I want to do is write a constraint (or 4 constraints) that says: If AttributeDataType = 'nvarchar', AttributeLength cannot be NULL, AttributePrecision must be NULL, AttributeScale must be NULL If AttributeDataType = 'int', AttributeLength must be NULL, AttributePrecision must be NULL, AttributeScale must be NULL If AttributeDataType = 'datetime', AttributeLength must be NULL, AttributePrecision must be NULL, AttributeScale must be NULL If AttributeDataType = 'decimal', AttributeLength must be NULL, AttributePrecision cannot be NULL, AttributeScale cannot be NULL Is that even possible? I've tried and failed (admittedly not for too long) to fathom a way of doing it. -Jamie
View Replies !
Check Constraint For 2 Fields
Hi , I have a table that holds CityName and TownName.This table is usually updated and I dont want the same CityName and TownName to be inserted Again. I have used Primary Key that holds thoose two fields but Primary Key is an index and I know that using check constraint works by spending less performance then indexes . So , How can I use a check Constraint for two fields ? Thanks ...
View Replies !
CHECK And CONSTRAINT Documentation
I am having some difficulties to find the documentation relative to the syntax of CHECK and CONTRAINT in MS SQL Server 2005. Can someone points me toward a reference guide? Thanks in advance, Joannès http://www.peoplewords.com
View Replies !
Questoin On Check Constraint
Hi there, My problem should be fairly simple. Say i have a small table with 2 columns, employee_id and department_id. I want to create a check constraint so that no more than 4 employee_id can be associated with department_id and the user is warned if he/she is trying to do so. Can anyone advise me on how to do it? Regards, Kam
View Replies !
Check Constraint Question
Hi I have created a table which contains date periods. It has 3 fields all with DateTime data types. These fields being ID, BeginDate, and EndDate. The ID field will hold a date indicating what month the reord is for, ie 01/03/2007 being March or 05/04/2007 being April. The BeginDate will contain the beginnig date for the financial month and EndDate for the financial month. I am trying to apply a check constraint on the ID field so that combaination of a month and year can only be entered once, ie. if 01/03/2007 already exists in the table then 03/03/2007 cannot. Is this possible? Thanking you in advance!
View Replies !
Check Constraint On Months
Hi I have a table with 3 columns in it. The ID column has a datetime data type. Does anyone know how to apply a check constraint on this field so that the same month cannot be entered twice. For example the field has the following data in the field 26/04/2008 27/05/2008 26/06/2008 25/07/2008 A user then tries to enter the value 20/05/2008, I would like the check constraint to block this value being entered due to the fact that a item with the same month value has already been entered. Is this possible? Thanking you in Advance!!!
View Replies !
Unique / Check Constraint?
I have a table called tblImages with the following columns: ImageID [int] UserID [int] MainImg [bit] what i need to ensure is that only one MainImage can be = 1 (true) for each userId at any one time. any idea what i need to do? alex
View Replies !
SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name]. The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions. 1 ALTER PROCEDURE dbo.sp_CreateUser 2 3 @UserID uniqueidentifier, 4 @UserName nvarchar(128), 5 @Email nvarchar(50), 6 @FirstName nvarchar(25), 7 @LastName nvarchar(50), 8 @Teacher nvarchar(25), 9 @GradYr int 10 11 AS 12 SET NOCOUNT ON; 13 --DECLARE @UserID uniqueidentifier 14 --SELECT @UserID = NULL 15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId 16 INSERT INTO [table] 17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) 18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr
View Replies !
Unique Constraint Does Not Permit Duplicate NULL Values
After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed. According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique. The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr) Thanks for any and all suggestions
View Replies !
Check Constraint SQL Server 2005
I want to set up a simple check constraint on a column limiting to values "Yes", "No" and ""I'm trying to use:CONSTRAINT IsAccessToItRestricted_ckcheck (IsAccessToItRestricted in('Yes,'No','');but this is not the right syntax.............. help!
View Replies !
TEXT Datatype With CHECK Constraint
Hi, I require a column to be created with CHECK constraint NOT NULL which has TEXT datatype. But SQL Server will not allow CHECK constraints for the columns which has TEXT datatype. How can I solve this problem ? Is there any other alternative for this ? With Warm Regards, Sam.
View Replies !
Check Constraint To Mimick Relationship
Hi, I'm faced with a dilemma regarding db design. I would like to have a generic table that links to many tables, but somehow still enforce integrity with a constraint. Eg An ownership table, then various document tables eg Loan document table, budget document table. Both the load table and budget table are linked to the ownership table by their primary keys. Ownership will have doc_id and doc_type columns. How do I enforce integrity ie the load_id = doc_id where doc_type = 'LOAN' and budget_id = doc_id where doc_type = 'BUDGET' are valid using a contraint? I believe check contraints cannot span across tables, is there a work around? I know a simple solution would be to use separate ownership tables, but I would like to make it generic. Many Thanks Paul
View Replies !
ALTERing A Column CHECK Constraint
How do I alter a column check constraint? I have the table: CREATE TABLE Mytable( mykey integer, mycol integer CHECK(mycol BETWEEN 1 AND 2) PRIMARY KEY(mykey)) How do I change the constraint to CHECK(mycol BETWEEN 0 AND 2) ...without losing any data? Thanks! Jim
View Replies !
CHECK Constraint - Referencing Another Column
I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas??? Thanks in advance! Server: Msg 8141, Level 16, State 1, Line 1 Column CHECK constraint for column 'end_date' references another column, table 'Session'. Here's an example of the script that I'm using: CREATE TABLE Session ( session_key char(18) NOT NULL, course_key char(18) NOT NULL, site_key char(18) NOT NULL, instructor_key char(18) NOT NULL, start_date smalldatetime NULL, end_date smalldatetime NULL CHECK (end_date >= start_date) ) .
View Replies !
Check Constraint On Part Of Column
Can anyone please tell me how can i create a uniqueness contraint on part of column and index that part too. i.e. consider the following table. table A Col1 furadfaf fsradfasd dddafadsf hjfhdfjakdj now i want only left three characters of the Col1 to be unique and indexed. any idea ??????
View Replies !
Implementing Check Constraint From Trigger
I know that SQL Server doesn't support subqueries in check constraint. So how do I then implement the check constraint. I tried using a trigger but I'm having difficulties with the syntax. Could someone help me. Here's the check constraint check(((Ph_P1Min is null)or(Ph_P1Max is null)or(Ph_P1Min<=Ph_P1Max))and((Ph_P1Min is null)or((select Ac_Calculated from Action where Ac_Id=Ph_Action)=1) or(Ph_P1Min<=all(select Pc_P1 from Procedure where Pc_Phase=Ph_Id))) and((Ph_P1Max is null) or((select Ac_Calculated from Action where Ac_Id=Ph_Action)=1) or(Ph_P1Max>=all(select Pc_P1 from Procedure where Pc_Phase=Ph_Id))) and(((select Ac_Calculated from Action where Ac_Id=Ph_Action)=0) or(Ph_RawMat is not null))) and here's the trigger that I converted it to create trigger trInsUpd_Phase on Phase for insert, update as if (select * from inserted, Action, Procedure where (((inserted.Ph_P1Min is null)or(inserted.Ph_P1Max is null)or(inserted.Ph_P1Min <= inserted.Ph_P1Max))and ((inserted.Ph_P1Min is null)or((select Ac_Calculated from Action where Action.Ac_Id = inserted.Ph_Action) = 1)or(inserted.Ph_P1Min <= all(select Pc_P1 from Procedure where Procedure.Pc_Phase = inserted.Ph_Id)))and((inserted.Ph_P1Max is null)or((select Ac_Calculated from Action where Action.Ac_Id = inserted.Ph_Action) = 1)or(inserted.Ph_P1Max >= all(select Pc_P1 from Procedure where Procedure.Pc_Phase = inserted.Ph_Id)))and(((select Ac_Calculated from Action where Action.Ac_Id = inserted.Ph_Action) = 0)or(inserted.Ph_RawMat is not null)))) = 0
View Replies !
Check Constraint On Character Column
When generating a check constraint to guarantee that a character column cannot be blank is it best to use comparison operators such as col1 <> '' or to use LEN(col1) > 0? Note that the column in marked as not nullable.
View Replies !
Error Validating Any Check Constraint
Hey Guys, I just this day started using SQL sever 2005. I created a database and then created a table. Then I started adding some fields. I wanted to add a check constraint to one of the fields called state but I keep getting the same error. I right click on the field while editing the table and select check constraint. I then click add on the check constraint dialogue and in the expression caption I input the following: <code> @State In('CA', 'AZ', 'UT', 'CO') </code> I am using a book and have straight copied the above example from the book. However when I input the check constraint I get the following error; "Error validating constraint 'ck_myfirstdatabase' I have tried this with other fields and other types of check constraints and I still get the same error. I have tried to delete the database and recreate it. I have tried everything I can think of and I cannot seem to get check constraints to work. I have no idea why I keep getting this message. I have checked the examples a thousand times, the syntax is definately correct. This is getting extremely annoying as I cannot continue unless I do this. I'm all out of ideas. Can anyone please tell me why it could not be working? Any ideas would be greatly appreciated.
View Replies !
Problem In Expression Of A Check Constraint
Hi all, I wrote the expression below in a table's check constraint (CustomerTypeID = 0 and ContentSiteID is null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null ) or ( CustomerTypeID = 1 and ContentSiteID is not null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null ) It saved it ok, but when I re-opened and viewed the expression again it was converted to: ([CustomerTypeID] = 0 and [ContentSiteID] is null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null [CustomerTypeID] = 1 and [ContentSiteID] is not null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null ) This is a different logic because the OR is not prioritised above the AND. Any solution or should I move my condition to the table's Update and Insert triggers? Thanks! Ofer
View Replies !
Check Constraint User Notification
Hi I've created a check constraint using the dialogue box in SQL Server , which blocks out the insertion of records which do not meet the required the requirements. Is there a way to be notified when the record has not been inserted? Thanks
View Replies !
Check Constraint Does Not Work (compare With Null)
Hi!I have a table with a check constraint. But unfortunately it does notwork like I wanted.CREATE TABLE MAP([R_ID] [T_D_ID] NOT NULL,[R_ID1] [T_D_ID] NULL,CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID]))R_ID1 should always have the value of R_ID or NullThe following statements should cause errors:insert into map (R_ID, R_ID1)values(1,2);update map set R_ID1=3 where R_ID=1;But there occur no errors. Does anyone have an idea? It is an SQL Server2000.TIASusanne
View Replies !
|
TRACKER
