Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi i need a table as follows
ID, Accno, Type
1 , 1 , 1
1 , 1 , 2
1 , 1 , 3
1 , 2 , 1 it can not be allowed
2 , 1 , 1 it can be allowed
how can i create this type of table?
ID cannot be used for any Other Account Number but it can be used multiple times for same Account Number??
Thanks in advance
Madhukumar N

[edit]Code block and a quick tidy of the data - OriginalGriff[/edit]
Posted
Updated 11-Apr-11 0:16am
v2
Comments
Sunasara Imdadhusen 11-Apr-11 6:16am    
Not clear!!
maddykumfa 11-Apr-11 6:24am    
The combination of ID and Account Number can be used Multiple times for Different types 1, 2 and 3
but the same id cannot be duplicated with any other account no. such as ID 1 AccNo 2 datatype 1

If you want to do this on server side, you can use a trigger to check this. See CREATE TRIGGER[^]

For example something like:
CREATE TRIGGER trg_MyTable FOR INSERT, UPDATE AS
BEGIN
   DECLARE @count int;
   SELECT @count = COUNT(*) FROM MyTable WHERE ... //insert the logic what row existences to check
   IF @count > 0 BEGIN
      RAISERROR('Some error message', 10, 1);
   END;
END;
 
Share this answer
 
You can't create a table that will enforce this automatically.

You will have to apply the rules manually, either by creating INSERT and UPDATE stored procedures or by doing it in code when you try to create / modify the records.

SQL does not have those kind of rules available for automatic record validation.

In addition, you will need a record ID row: you cannot use the same ID for multiple records as well.
 
Share this answer
 
See Frnd Id should be Unique and it Should be a Primary key.Duplicate Id it sholud not be allowed It a Satandard of the Database
 
Share this answer
 
Can u have something like this may be.

Table1

PK_ID,ID , AccNO
1, 1, 1
2, 2, 1
3, 3, 2

Table2

Type, PK_ID
1, 1
2, 1
3, 1
 
Share this answer
 
If I understand correctly you need two constraints:
1. unique by all columns
SQL
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [IX_MyTable_1] UNIQUE NONCLUSTERED
(
    [ID] ASC,
    [Accno] ASC,
    [Type] ASC
)


2. unique by ID/Type
SQL
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [IX_MyTable_2] UNIQUE NONCLUSTERED
(
    [ID] ASC,
    [Type] ASC
)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900