Unique Records - 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

Unique Records

Author

Topic

gaggel
Starting Member

8 Posts

Posted - 2009-12-03 : 00:38:42

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
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-03 : 06:43:41

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.

gaggel
Starting Member

8 Posts

Posted - 2009-12-08 : 02:04:55

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?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-12-11 : 23:45:06

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.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-14 : 08:21:22

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.

gaggel
Starting Member

8 Posts

Posted - 2009-12-14 : 13:57:23

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.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 05:30:48

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.

gaggel
Starting Member

8 Posts

Posted - 2009-12-15 : 07:01:18

"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

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 07:21:09

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.

gaggel
Starting Member

8 Posts

Posted - 2009-12-16 : 01:20:23

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.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-16 : 05:39:21

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.

gaggel
Starting Member

8 Posts

Posted - 2009-12-21 : 16:47:39

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.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-22 : 08:56:55

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.

gaggel
Starting Member

8 Posts

Posted - 2010-01-06 : 07:36:57

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)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2010-01-06 : 07:50:03

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources