Adding Constraints - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Adding Constraints

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-15 : 18:07:31

CREATE TABLE [dbo].[tbl_type]
(
[type_id] [int] NOT NULL ,
[types] [varchar] (60) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_type_code]
(
[type_code_id] [int] NOT NULL ,
[type_id] [int] NOT NULL ,
[description] [varchar] (60) NOT NULL ,

) ON [PRIMARY]

type_id is a foreign key constraint...

Insert into tbl_type
(type_id,types)
select 1,'Employee Types' UNION ALL
select 2,'Contact Types' UNION ALL
select 3,'Income Types'

Insert into tbl_type_code
(type_code_id,type_id,description)
select 1, 1, 'V.P' UNION ALL
select 2, 1, 'Director' UNION ALL
select 3, 1, 'Program Manager' UNION ALL
select 4, 1, 'Senior Manager' UNION ALL
select 5, 1, 'Business Analyst' UNION ALL
select 6, 1, 'Developer' UNION ALL

select 7, 2, 'Cell' UNION ALL
select 8, 2, 'Home' UNION ALL
select 9, 2, 'Work'

select 10, 3, 'Monthly'UNION ALL
select 11, 3, 'Daily' UNION ALL
select 12, 3, 'Weekly' UNION ALL
select 13, 3, 'Hourly'

Now I have 2 other tables :-
tbl_emp_detail

CREATE TABLE [dbo].[tbl_emp_detail]
(
[emp_id] [int] NOT NULL ,
[emp_type_code_id] [int] NOT NULL ,
[emp_name] [varchar] (60) NOT NULL ,
[emp_age] [int] NOT NULL ,
[emp_address] [varchar] (60) NOT NULL ,
[emp_contact_type_code_id] [INT] NOT NULL
) ON [PRIMARY]

Here How do I create a constraint where emp_type_code_id is tied to
the type_code_id.IF I create a foriegn key constraint then it will allow other type_code_id also to be inserted and I want only for the emp_type_code_id's of the employee types to be inserted.

Similarly the emp_contact_type_code_id

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-07-15 : 18:27:37

I see 4 options..

1) Create redundant keys and apply FK's.
2) Create a UDF based constraint .
3) Create a updateable/checked view
4) Re-design.

I like option 4..

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-15 : 19:20:09

I can't re-design because the model is already out there and the development is already done based on this model.So I have the only option adding constraints.Which one should I opt

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-15 : 20:23:55

Your problem is caused because you should have the type_id wherever the type_code_id appears.
As you have it how do you know which of the type_id's the type_code_id refers to? Not by any data in the database hence you can't constrain it without extra structures.

You could do this with a trigger - hard coding the type_id.
create trigger x on tbl_emp_detail for insert, update
as
if exists (select * from inserted i left join tbl_type_code c on i.emp_type_code_id = c.type_code_id and c.type_id = 1 where c.type_code_id is null)
begin
raiserror('invalid id', 16, -1)
rollback tran
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-17 : 11:21:20

This thread reminds me of this article:

http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp

I recommend you get whoever did the design to read that article in a hope they don't make the same mistake next time.

For me, the correct design would be to split the tbl_type_code into 3 separate tables (given the data you have supplied).

My suggestion would be to switch to that design, but create some 'backward compatibility'. Something along the following lines...

--new design and data
CREATE TABLE dbo.tbl_EmployeeType (emp_type_code_id int NOT NULL primary key, description varchar(60) NOT NULL)
insert dbo.tbl_EmployeeType
select 1, 'V.P' UNION ALL
select 2, 'Director' UNION ALL
select 3, 'Program Manager' UNION ALL
select 4, 'Senior Manager' UNION ALL
select 5, 'Business Analyst' UNION ALL
select 6, 'Developer'

CREATE TABLE dbo.tbl_ContactType (emp_contact_type_code_id int NOT NULL primary key, description varchar(60) NOT NULL)
Insert dbo.tbl_ContactType
select 7, 'Cell' UNION ALL
select 8, 'Home' UNION ALL
select 9, 'Work'

CREATE TABLE dbo.tbl_IncomeType (emp_income_type_code_id int NOT NULL primary key, description varchar(60) NOT NULL)
Insert dbo.tbl_IncomeType
select 10, 'Monthly'UNION ALL
select 11, 'Daily' UNION ALL
select 12, 'Weekly' UNION ALL
select 13, 'Hourly'

CREATE TABLE [dbo].[tbl_emp_detail] 
(
[emp_id] [int] NOT NULL ,
[emp_type_code_id] [int] NOT NULL references tbl_EmployeeType (emp_type_code_id),
[emp_name] [varchar] (60) NOT NULL ,
[emp_age] [int] NOT NULL ,
[emp_address] [varchar] (60) NOT NULL ,
[emp_contact_type_code_id] [INT] NOT NULL references tbl_ContactType (emp_contact_type_code_id)
) ON [PRIMARY]

--'Backward compatibility' code
CREATE TABLE [dbo].[tbl_type] ([type_id] [int] NOT NULL, [types] [varchar] (60) NOT NULL) ON [PRIMARY]
Insert into tbl_type (type_id,types)
select 1,'Employee Types' UNION ALL
select 2,'Contact Types' UNION ALL
select 3,'Income Types'

go
CREATE view [dbo].[tbl_type_code] as
select emp_type_code_id as type_code_id, 1 as type_id, description from dbo.tbl_EmployeeType union all
select emp_contact_type_code_id, 2, description from dbo.tbl_ContactType union all
select emp_income_type_code_id, 3, description from dbo.tbl_IncomeType

go
create trigger tr_tbl_type_code_instead_of_insert on tbl_type_code instead of insert as
begin
    insert dbo.tbl_EmployeeType
    select type_code_id, description from inserted where type_id = 1
    
    insert dbo.tbl_ContactType
    select type_code_id, description from inserted where type_id = 2

    insert dbo.tbl_IncomeType
    select type_code_id, description from inserted where type_id = 3
end
go

--example 'backward compatibility' usage
insert tbl_type_code
select 14, 1, 'System Architect' union all
select 15, 2, 'Home2'

select * from tbl_type_code

--tidy up
go
drop table dbo.tbl_emp_detail
drop table dbo.tbl_type
drop view dbo.tbl_type_code
drop table dbo.tbl_EmployeeType
drop table dbo.tbl_ContactType
drop table dbo.tbl_IncomeType

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources