Constraints /Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Constraints /Triggers

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-04 : 20:56:59

I have 2 tables

create table A1
(colA int ,
colB int,
colC int
constraint pk primary key (colA)
)
create table A2
(
col1 int,
col2 int,
col3 int
)

Now for every row in table A2 i have to check if there is a row in A1 with col2=ColB.

foreign key will not work since the colB is not unique in tableA1.

Is there any way for me to do it other than Triggers? I have to write dtsx but how do I check the data integrity?
Will triggers slow down my process?

Thanks

dportas
Yak Posting Veteran

53 Posts

Posted - 2010-03-05 : 02:28:33

Create another table:
CREATE TABLE B (colB INT NOT NULL PRIMARY KEY);

Now put foreign key constraints on A1.colB and A2.col2 that reference the new table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:25:04

quote:
Originally posted by AAAV

I have 2 tables

create table A1
(colA int ,
colB int,
colC int
constraint pk primary key (colA)
)
create table A2
(
col1 int,
col2 int,
col3 int
)

Now for every row in table A2 i have to check if there is a row in A1 with col2=ColB.

foreign key will not work since the colB is not unique in tableA1.

Is there any way for me to do it other than Triggers? I have to write dtsx but how do I check the data integrity?
Will triggers slow down my process?

Thanks

you can use a check constraint based on udf also if you want. just create a udf to return count of records from A1 based on value of colB=t2.col2 and then make a check constraint on condition count returned > 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 13:24:43

i thought of the check constaint and the UDF but wont that make the load slow when i am loading min 100000 records?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources