Data Integrity Question

I have the following table:
CREATE TABLE [transactions] (
[Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[Effec_date] [smalldatetime] NULL ,
[Amount] [money] NULL ,
[Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Accuracy_disclaimer] [bit] NULL
CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
(
[Transaction_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---
I want to prevent a record from being inserted where a record already exists
based on the following columns:
--CID
--Effec_Date
--Amount
In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006'
and
Amount = 1.50, I want to prevent another record with these values from being
inserted. Would I use a muti column check constraint? What is the syntax?In a similar fashion as you have the PRIMARY KEY constraint, add a UNIQUE co
nstraint over those
three columns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
>I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already exis
ts
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006
'
> and
> Amount = 1.50, I want to prevent another record with these values from bei
ng
> inserted. Would I use a muti column check constraint? What is the syntax
?
>|||create a unique constraint on these 3 columns
create table t_1
(
col1 int,
col2 int,
col3 int
)
alter table t_1 add constraint UQ_col1_col2_col3 unique (col1,col2,col3)
go
"Eric" wrote:

> I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already exis
ts
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date = '04/01/2006
'
> and
> Amount = 1.50, I want to prevent another record with these values from bei
ng
> inserted. Would I use a muti column check constraint? What is the syntax
?
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
>I have the following table:
> CREATE TABLE [transactions] (
> [Transaction_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [CID] [int] NULL ,
> [Effec_date] [smalldatetime] NULL ,
> [Amount] [money] NULL ,
> [Transaction_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Trans_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Accuracy_disclaimer] [bit] NULL
> CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
> (
> [Transaction_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ---
> I want to prevent a record from being inserted where a record already
> exists
> based on the following columns:
> --CID
> --Effec_Date
> --Amount
> In other words, if I have a record where CID = 1, Effec_Date =
> '04/01/2006'
> and
> Amount = 1.50, I want to prevent another record with these values from
> being
> inserted. Would I use a muti column check constraint? What is the
> syntax?
>
This is a UNIQUE constraint. I suggest you make those columns non-nullable
first though:
ALTER TABLE transactions
ALTER COLUMN cid INTEGER NOT NULL;
ALTER TABLE transactions
ALTER COLUMN effec_date SMALLDATETIME NOT NULL;
ALTER TABLE transactions
ALTER COLUMN amount MONEY NOT NULL;
/* Add the unique constraint */
ALTER TABLE transactions
ADD CONSTRAINT ak1_transactions
UNIQUE (cid, effec_date, amount);
BTW. The MONEY datatype is typically a poor choice for amounts that will be
used in calculations. Using MONEY can lead to rounding errors. Use DECIMAL
instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, noticed the rounding issue w/money. Thanks for the tip(s).
"David Portas" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:0AFE3D9C-6934-44A8-9160-CEDE47D2C76A@.microsoft.com...
> This is a UNIQUE constraint. I suggest you make those columns non-nullable
> first though:
> ALTER TABLE transactions
> ALTER COLUMN cid INTEGER NOT NULL;
> ALTER TABLE transactions
> ALTER COLUMN effec_date SMALLDATETIME NOT NULL;
> ALTER TABLE transactions
> ALTER COLUMN amount MONEY NOT NULL;
> /* Add the unique constraint */
> ALTER TABLE transactions
> ADD CONSTRAINT ak1_transactions
> UNIQUE (cid, effec_date, amount);
> BTW. The MONEY datatype is typically a poor choice for amounts that will b
e
> used in calculations. Using MONEY can lead to rounding errors. Use DECIMAL
> instead.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||You actually do not have a table at all, so you can NEVER have data
integrity. IDENTITY cannot be a key by definition and alll the other
columns are NULL-able and have no constraints.
Literally every row is wrong according to the basic rules of RDBMS, ISO
rules and data modeling.
CREATE TABLE Transactions - vague name!!
(transaction_id INTEGER IDENTITY (1, 1) NULL - non-relatioanl ,
non-key!! Total crap
cid INTEGER NULL, nulls in an identifier? Duh!
effect_date DATETIME NULL, -- no default?
amount MONEY NULL, -- proprietary data type and bad name!!
transaction_type VARCHAR (25) NULL, -- variable length & ISO-11179
violation
trans_code NVARCHAR (50) NULL, --really? codes in Chinese?
accuracy_disclaimer BIT NULL --bits in SQL?
);
Please give me an example of a transaction_type that is CHAR(50); if
you can find one, we need to re-design this encoding, unless it is an
industry standard.
Why did you use MONEY? Do you spit on ANSI./ISO Standards or do you
want the math problems?
Why did you think that BIT is a SQL data type or valid way to program
once you get above the assembly language level?
I have to use this in a book. When I try to make up a bad example, I
keep correcting gross errors; you do not!!
.
based on the following columns:
--CID
--Effec_Date
--Amount <<
Then put them into a key or a UNIQUE constraint!! Duh! And learn that
a row is not anything like a record.