SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Unique Records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gaggel
Starting Member

Greece
8 Posts

Posted - 12/03/2009 :  00:38:42  Show Profile  Reply with Quote
Hi colleagues

I'm trying to design a db from scratch and I want to determine unique records to any table.
In some tables it is not possible to do it with just one field (column) and I need to determine 2 or more fields as one field in order to play the role of the primary key of the table.

E.g. for an application (p. key: 3) which is installed to this pc (p. key: 6) I have fix these problems:
3,6: wertyuiop (first record)
3,6: sdfghjklj (second record)
3,6: xcvbnmnm (third record)

For the same application in another pc I can have:
3,8: thjkiol (first record)
3,8: sasasas (second record)

So this combination of "Application_ID + PC_ID" can work as a primary key in detail table.

Does anyone know how can I declare it in the MS SQL?

Thank you all, for your time in advance.

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 12/03/2009 :  06:43:41  Show Profile  Reply with Quote
This?

create table MyTable (Application_ID int, PC_ID int, AnotherColumn varchar(50) primary key (Application_ID, PC_ID))

--or
create table MyTable (Application_ID int not null, PC_ID int not null, AnotherColumn varchar(50))
alter table MyTable add primary key (Application_ID, PC_ID)


Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

gaggel
Starting Member

Greece
8 Posts

Posted - 12/08/2009 :  02:04:55  Show Profile  Reply with Quote
Thnak you for your answer

I tried to work this code but is hasn't the results that I need.
Maybe, I have not give you the right problem.

So, the problem is that I want to have unique records with combined fields.
As an example:
I want to record all the applications that are installed on a server or a pc.
E.g. server1 (1) - SAP(1) = inique record "11" (as a combined field)
server1 (1) - Medilab (2) = inique record "12" (as a combined field)

If I will try - by a mistake - to record again that the server 1 has installed the application SAP, I want the appropriate detail table to take the combined field "11" as one field and to prevent a second record with the same combination "11".

Does someone know a code to solve this problem?
Go to Top of Page

sapator
Constraint Violating Yak Guru

Greece
318 Posts

Posted - 12/11/2009 :  23:45:06  Show Profile  Reply with Quote
How about.

declare @value1 int
declare @value2 int
declare @value3 int

set @value1 = (select sap from pc where serverid  =1)
set @value2 = (select medlab from pc where serverid  =1)

set @value3 = @value1 + @value2

The above is a view so convert to sp

Then

IF EXISTS (SELECT valuex FROM pccombinedresults WHERE Column1=@value3)
    --don't do anything
ELSE
    INSERT INTO pccombinedresults VALUES (...)


Something like that.

Edited by - sapator on 12/12/2009 00:27:11
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 12/14/2009 :  08:21:22  Show Profile  Reply with Quote
I don't understand why you think you need a single column for your primary key. Are you aware that both a primary key and a foreign key can consist of more than one column?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

gaggel
Starting Member

Greece
8 Posts

Posted - 12/14/2009 :  13:57:23  Show Profile  Reply with Quote
Yes. I’m aware that both a primary key and a foreign key can consist of more than one column.

A unique record in one of my detail tables can be a combination of 3 or 4 other columns (that can be foreign keys or no) like: 1, 8, 6, 23
This combination means:
Detail table: PC_Connections
The pc (1) is connected to the switch (8) which connected to the server (6) and belongs to the audit department (23). This is a combination with foreign keys.

I want this combination of columns to be unique; in order to be prevented a duplicated record (in the example: the connection of the pc (1)).

Thank you for your interesting to help me.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 12/15/2009 :  05:30:48  Show Profile  Reply with Quote
quote:
I want this combination of columns to be unique
Then I don't understand why you can't use a primary key or a unique index on those columns.

Could you post your table structure (for all the relevant tables) and some sample data?

Maybe you mean 1, 8, 6, 23 means you can't have 1, 8, 6, 17 or 1, 8, 12, 23 or 2, 8, 6, 23?


Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

gaggel
Starting Member

Greece
8 Posts

Posted - 12/15/2009 :  07:01:18  Show Profile  Reply with Quote
"Maybe you mean 1, 8, 6, 23 means you can't have 1, 8, 6, 17 or 1, 8, 12, 23 or 2, 8, 6, 23?"

No. I mean that: I don't want to have the same combination of values 1 8 6 23

But I can have this combination 1 7 6 23 which means that:
The pc (1) has a second net card and connected through another switch (7) to the server (6) and belongs to the audit department (23).

This can happen due to DR provisions.

If this is not understandable I can send you a small schema (4-5 tables).

Thank you
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 12/15/2009 :  07:21:09  Show Profile  Reply with Quote
Your answer to my stab in the dark is understandable, but the reason for that stab in the dark is still not addressed: I still don't understand why you can't use a primary key or a unique index on these columns.

Please post your table structure and some sample data.


Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

gaggel
Starting Member

Greece
8 Posts

Posted - 12/16/2009 :  01:20:23  Show Profile  Reply with Quote
Good morning my friend

I can send you a table structure and some sample data in a word document, but I can not attach it here. It will be easier to understand it.

May I send it to your email?
(If not I will try to post it here).

Thank you.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 12/16/2009 :  05:39:21  Show Profile  Reply with Quote
You should try to post it so others can see it too. Here's some advice on what to post:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

gaggel
Starting Member

Greece
8 Posts

Posted - 12/21/2009 :  16:47:39  Show Profile  Reply with Quote
Good evening friends

As the previous post suggests, I'm giving the problem and the code.

THE PROBLEM IS:
How can I have Unique Records in a table with combined fields of the table?

EXAMPLE OF THE PROBLEM:

In the Detail Table: Applications_installed


PK table PCs----------PK table APPs----------------Detail Table Apps_instl
pc_id (1)-------------app_id (1) (win xp)------------1 1
pc_id (1)-------------app_id (2) (office 2003)-------1 2
pc_id (1)-------------app_id (3) (visio)-------------1 3
pc_id (1)-------------app_id (2) (office 2003)-------1 2 this is the duplicate record that I want to prevent and this record (1 2) is a combination of the fields pc_id & app_id.

CREATION OF THE TABLES
Table of Applications
applications CREATE TABLE `applications` (
`app_id` int(11) NOT NULL AUTO_INCREMENT,
`app_no` int(11) NOT NULL,
`app_name` char(30) COLLATE utf8_unicode_ci NOT NULL,
`app_namuf` int(11) NOT NULL,
`app_serno` char(30) COLLATE utf8_unicode_ci NOT NULL,
`app_sysown` int(11) NOT NULL,
`app_deput_sysown` int(11) DEFAULT NULL,
`app_dataown` int(11) NOT NULL,
`app_deput_datown` int(11) DEFAULT NULL,
`app_SLA` int(11) NOT NULL,
`app_lis_no` char(30) COLLATE utf8_unicode_ci NOT NULL,
`app_docum` text COLLATE utf8_unicode_ci,
`app_scope` text COLLATE utf8_unicode_ci,
`app_rem` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`app_id`),
KEY `app_namuf` (`app_
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


Table of PCs
CREATE TABLE `pcs` (
`pc_id` int(11) NOT NULL AUTO_INCREMENT,
`pc_no` int(11) NOT NULL,
`pc_name` char(30) COLLATE utf8_unicode_ci NOT NULL,
`pc_manuf` int(11) NOT NULL,
`pc_partno` char(30) COLLATE utf8_unicode_ci NOT NULL,
`pc_serno` char(30) COLLATE utf8_unicode_ci NOT NULL,
`pc_model` char(30) COLLATE utf8_unicode_ci NOT NULL,
`pc_acquis_dat` date NOT NULL,
`pc_cpu_freq` float NOT NULL,
`pc_mon` int(11) NOT NULL,
`pc_net` int(11) NOT NULL,
`pc_os` int(11) NOT NULL,
`pc_loc` int(11) NOT NULL,
`pc_sysown` int(11) NOT NULL,
`pc_deput_sysown` int(11) DEFAULT NULL,
`pc_SLA` int(11) NOT NULL,
`pc_bios_pssd` char(30) COLLATE utf8_unicode_ci NOT NULL,
`pc_boot_pssd` char(30) COLLATE utf8_unicode_ci NOT NULL,
`pc_docum` text COLLATE utf8_unicode_ci,
`pc_rem` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`pc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


Table of Applications_Installed
pc_appsinstl CREATE TABLE `pc_appsinstl` ( `pc_appsinstl_id` int(11) NOT NULL AUTO_INCREMENT, `pc_appsinstl_appid` int(11) NOT NULL, `pc_appsinstl_pcid` int(11) NOT NULL, `pc_appsinstl_rem` text COLLATE utf8_unicode_ci, PRIMARY KEY (`pc_appsinstl_id`), KEY `pc_appsinstl_appid` (`pc_appsinstl_appid`), KEY `pc_appsinstl_pcid` (`pc_appsinstl_pcid`), CONSTRAINT `pc_appsinstl_ibfk_1` FOREIGN KEY (`pc_appsinstl_appid`) REFERENCES `applications` (`app_id`), CONSTRAINT `pc_appsinstl_ibfk_2` FOREIGN KEY (`pc_appsinstl_pcid`) REFERENCES `pcs` (`pc_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci



INSERTION OF THE VALUES
FOR THE TABLE Applications
"INSERT INTO applications (app_no, app_name, app_namuf, app_serno, app_sysown, app_deput_sysown, app_dataown, app_deput_datown, app_SLA, app_lis_no, app_docum, app_scope, app_rem)
VALUES
('4','medilab','6','123-456-789-000','2','1','5','1','9','30','test text','test text','test text')";

FOR THE TABLE PCs
"INSERT INTO pcs (pc_no, pc_name, pc_manuf, pc_partno, pc_serno, pc_model, pc_acquis_dat, pc_cpu_freq, pc_mon, pc_net, pc_os, pc_loc, pc_sysown, pc_deput_sysown, pc_SLA, pc_bios_pssd, pc_boot_pssd, pc_docum, pc_rem)
VALUES
('2','pc135','2','123456-987','123-4546-7899000-00','hppcx-9000','2009/12/19','2.4','3','3','3','3','2','1','2','pan','pan','test text','test text')";

FOR THE TABLE Applications_Installed
"INSERT INTO pc_appsinstl (pc_appsinstl_appid, pc_appsinstl_pcid, pc_appsinstl_rem) VALUES ('3','5','test text')";



These three tables are a good example of the problem that I have to fix in order to prevent duplicated records.
In ORACLE this problem can be fixed with TYPE order, where you can set 2 or 3 or more fields of a table as a TYPE (e.g. apps_inst_type) and then these fields are manipulated as one field. So, you can declare this "one" filed as Index or PK or anything.

Thank you for your time to help me in MS SQL.
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 12/22/2009 :  08:56:55  Show Profile  Reply with Quote
Is this Microsoft SQL Server?

Anyway, I can see you have an identity (auto increment) as the primary key on the pc_appsinstl table, so you will need to use a unique constraint rather than a primary key. Try...

ALTER TABLE pc_appsinstl ADD CONSTRAINT UQ_PCId_AppId UNIQUE (pc_appsinstl_appid, pc_appsinstl_pcid)


Here's a full example using MyTable...

create table MyTable (MyTableId int identity primary key, Application_ID int not null, PC_ID int not null, AnotherColumn varchar(50))
ALTER TABLE MyTable ADD CONSTRAINT UQ_PCId_AppId UNIQUE (Application_ID, PC_ID)

insert MyTable (Application_ID, PC_ID) values (1, 1)
insert MyTable (Application_ID, PC_ID) values (1, 2)
insert MyTable (Application_ID, PC_ID) values (1, 3)
insert MyTable (Application_ID, PC_ID) values (1, 2) --this one fails to insert with "Violation of UNIQUE KEY constraint 'UQ_PCId_AppId'"


Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

gaggel
Starting Member

Greece
8 Posts

Posted - 01/06/2010 :  07:36:57  Show Profile  Reply with Quote
Good morning Ryan

Thank you for your answer.
The proposed solution has operated very well and now I add it to the tables of the DB.
This solution was too simple but I didn't know how to express my problem in the MS SQL code!!!
Anyway I want to thank you for your help. It was very important for me.

George
(Greece)
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 01/06/2010 :  07:50:03  Show Profile  Reply with Quote
Hi George

Very kind of you. Thank you.


Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000