How To Implement Unique Key On Multiple Field

Sep 5, 2006

hello guys,

I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table).  Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?

thank you.

View 1 Replies


ADVERTISEMENT

Need Help W/ SELECT From One Table, One Field, Multiple Unique Records

Apr 22, 2006

I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:

Anna Smith
John Doe

etc. I did not see IDs listed for any of the names in the table when I looked.

There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.

Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:

strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"

This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.

Is there a way to modify this statement to pull only the records that I want?

Thanks for any help you can give.

AJ

View 3 Replies View Related

Integration Services :: How To Implement SCD For No Unique Columns In A Table

May 11, 2015

I would like to know what is the use of business key? Is it necessary to have unique key between source and destination? If no, How can we implement SCD?

PS : My source is CSV files and Destination is Oracle DB?

View 2 Replies View Related

How To Implement Alter Database Implement Restrictions On SQL2K ?

Dec 28, 2007

Hi Guyz

it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2

"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:

Explicitly denying the ALTER DATABASE statement.


A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.


If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."

thanks in advance.

View 4 Replies View Related

Need A Unique Bit Field

May 31, 2001

I have a bit field only alow one row as 1 all other rows should always 0.
How can I control that -- trigger or constraint?? How?

View 1 Replies View Related

Unique Field

Nov 17, 2004

I have a database table with a primary key that auto-increments - using MS SQL Server 2000.
However I have another field (standard field) that must not allow duplicates.

Besides writing code to sift through the database every time to check for duplicates (which would take long to search through the db every time), is there any setting in MS SQL Server 2000 that allows for an automatic check for duplication and will prevent duplicates!??

View 14 Replies View Related

Unique Field

Nov 18, 2004

I have a database table with a primary key that auto-increments - using MS SQL Server 2000.
However I have another field (standard field) that must not allow duplicates.

Besides writing code to sift through the database every time to check for duplicates (which would take long to search through the db every time), is there any setting in MS SQL Server 2000 that allows for an automatic check for duplication and will prevent duplicates!??

Here's an example of what I mean...

PK Entry_Sheet Cust_Id Date
1 62345 cust1 16/11/2004
2 45663 cust6 16/11/2004
3 82917 cust1 16/11/2004
4 19283 cust2 17/11/2004
5 28764 cust2 18/11/2004

I have everything created, with data already in the table. What I need now is some way to prevent duplicates from occurring.
The Entry_Sheet value needs to be unique. Instead of writing code to run through the entire db each time an insert is performed, is there a simpler way using a SQL Server DB!??

View 12 Replies View Related

Creating A Unique Field

Nov 30, 2000

I have built a table called Visit_Fact, which is detailed medical data. Within this table I have to create several index fields.

Well one of the fields ([Diag Code]) that should be included will have null values.

Well in Access I was able to build my new field called IndKey which has the following SQL statement:

IndKey: [PracticeCode]&[Physician Code]&[Chart #]&[Visit #]&[InsCoNum]&[CPT]&[Modifier]&[Units]&[Diag Code]

1. Example data with Diag Code = null
ABCD1234ABCDEF1234567MEDI19921301

2. Example data with Diag Code <> null
ABCD1234ABCDEF1234567MEDI19921301708.1

This runs fine in Access and if the Diag Code is null, Access does not care, it still puts all the other information together. Such as the examples above.

Well I tried doing this in a Stored Procedure and when I ran across a record that had a null value for the [Diag Code] it made the IndKey field Null for that record. If it was not null then it looked like Example 2.

What is the proper way of creating my unique field in SQL Server?

View 2 Replies View Related

Create Unique Field That Isn't The Key

Nov 10, 2005

I have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?

TIA

View 1 Replies View Related

A Table With A Unique Field

Jul 10, 2007

Hello,

I would like to store some frequently asked questions in a database.
As those questions/answers could be translated in several languages, I was thinking of using two tables :

The first table would contain a unique field : the key wich is an id for each faq.

The second table would contain those fields :
FAQ_ID : foreign key
LANG : language
QUESTION : the question translated in the good language.
ANSWER : the answer translated in the good language.

What I find embarrassing, is that first table just contains a unique field : the id. But it allows :
- to have a table in which a line correspond to an object. It is convenient for object relational mapping.
- it is more evolutionary. If, in the future, I have to add some statistics that concerns a faq, I will have the possibility to store them in the first table, avoiding in this way to duplicate data.

But, one more time, I find a bit embarrassing having a table with a unique field...

What is your opinion about that ?

Thank you in advance for any suggestion.

mathmax

View 20 Replies View Related

Unique Key With Optional Field

Jul 23, 2005

Is it possible to have the field as a unique key and a optional one?It is like.. for example, office code has to be unique (cannot beduplicated with the same code) and it could be null too.

View 1 Replies View Related

Unique Counter Field In A Table - Best Way?

Oct 11, 2005

I need to to have a table that has a counter field (used to generate an id code for a record).  This is not the primary id for the field but just a counter to label a record in another table, similar to a registration code for an event.  This is a sequential counter that will start 1 and go up on each new record inserted into other tables.  What I want to do is be able to query this counter table to get the next valid number and increment the count for the next time.  This is a multi user web app so needs to prevent duplicate use of same number.How best should this be handled?  A transaction to query and update the field?

View 6 Replies View Related

Count Unique Rows In Each Field

Jul 24, 2013

I have a table in Access 2007 that has about 30 field names and I want to have a count of how many unique rows there are in each field. I want to have these results put into another table that will just have the field name and then the count of how many unique rows there are.

I have code in VBA that will loop through my SQL and change out the field name, but I can't seem to get the SQL right before I can start looping it. For just one field name this would be what I have to count the unique names...

So far I have this:

INSERT INTO newtable
COUNT(*) FROM (SELECT Raw_Table.FieldName, COUNT(Raw_Table.FieldName) AS CountOfFieldName
FROM Raw_Table
GROUP BY Raw_Table.FieldName);

And its not going too well.

View 1 Replies View Related

Changing Field For Unique Key Index

Oct 18, 2007

SQL 2000 Enterprise Manager won’t let me drop a unique key index and move it to another field. I get an error regarding ‘drop index not allowed because of unique key constraint enforcement.

Is there anyway to change fields for unique keys in SQL Enterprise Manager?

Thanks, Al

View 4 Replies View Related

Unique Field Conflict In Views

Jul 20, 2005

I created a view that joins two tables (tblOrder and tblProduct). The fieldProductID is common to both. When I save the View I get the following errormessage: "Column names in each view must be unique." Is there a way aroundthat? Should I be naming my Key Fields and Foreign Key fields differently?I'm new to MS-SQL. Thanks for your help.Louis

View 2 Replies View Related

Finding Unique Field Names With Different Values

Jun 8, 2005

I am in a situation where I need to find out unique field names with different values in a table having 200+ columns.
Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values.
Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values.
I would appreciate If anybody gives me hint about achieving desired result
 

View 2 Replies View Related

How To Create Unique Field Or Sequence In View

Jun 5, 2012

I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.

View 13 Replies View Related

Running Sum Is A Unique ID Field - Balance On Certain Date?

Dec 20, 2012

I am working on a software primarily related to accounting with visual basic

Many of the problems and proposed several solutions for running sum, but I have read almost all of these interpretations,

have applied when running sum is a unique ID field. but almost all of the functioning of accounting reports are in chronological order. So he questioned what was the balance on 05.05.2012.

ID of the process is done, if the user enters a process backward a few things since then, the transaction date is old, but the ID is new, are experiencing problems. eg

ID DATE DEBIT CREDIT BALANCE

1 02.02.2012 100.00 0.00 100.00

6 04.04.2012 0.00 150.00 -50.00

3 02.05.2012 70.00 0.00 20.00

4 02.05.2012 80.00 0.00 100.00

2 06.06.2012 120.00 0.00 220.00

must balance the figure above.

if ID = 2 record, the record date 06/06/2012 and if the ID = 6, date 04/04/2012, the date the order is made if the listing is experiencing trouble getting balance. 2 records in the same day when the balance at worst formula breaks down. The big point is that I hang out in my project.

the only solution is to use a temporary table or writing about it in Visual Basic seems to calculate. the use of temporary table is not a solution, but unfortunately also very fast.

View 1 Replies View Related

Selecting Records Where Part-field Is Unique

Aug 10, 2015

I have a very large CSV file containing name-and-address information which I am reading in a Visual Basic project using the Microsoft.JetOLEDB.4.0 provider.

The key field on which the CSV file is to be filtered is the PostCode field.  This is a UK-format PostCode "XXnn Nxx" where "XX" is one or more letters denoting a geographical area within the UK and "nn" is one or more characters (starting with at least one numeric digit) which when combined with the area code denotes a specific district within the geographical area.  My aim is to identify all the unique UK postal districts held within my address CSV file.

Because I do not know how to use SQL to filter on the partial contents of a database field I am presently reduced to extracting unique full PostCodes using "SELECT DISTINCT PostCode,City,County FROM [ADDRESSES.csv]" into a DataTable object, then sequentially reading that DataTable using the operation of a dictionary object to identify unique PostCode areas, to finally construct the DataTable I need.

Is it possible in SQL to select records where the value of a varying number of characters before a space character in a given (PostCode) field is unique?

View 12 Replies View Related

Multiple Foreign Keys On Same Field, Based On Other Field

Jul 23, 2005

I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.

View 26 Replies View Related

Combine Multiple Field On The Basis Of Employee ID Field

Sep 30, 2015

I need formulating a view through which I can create a an output like below image -

Monthly Table

NUM STATUS ACTIVITYCODE

HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69

Season Table

NUM STARTMONTH STARTDAY ENDMONTH EMDDAY

HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28

[Code] ...

Final Output 

NUM STATUS ACTIVITYCODE <SEASONS>

HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November

I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.

select num, (CAST(startday AS VARCHAR(3)) + ' ' +  startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' +  endmonth)AS Season from seasons;

View 10 Replies View Related

SQL Server 2012 :: Unique Identifier As Identity Field

Dec 27, 2013

So for years I was using the int identity(1,1) primary_key for all the tables I created, and then in this project I decided, you know, I like the uniqueidentifier using newsequentialid() to ensure a distinctly unique primary key.

then, after working with the php sqlsrv driver, I realized huh, no matter what, i am unable to retrieve the scope_identity() of the insert

So of course I cruised back to the MSSMS and realized crap, I can't even make the uniqueidentifier an identity.

So now I'm wondering 2 things...

1: Can I short cut and pull the uniqueidentifier of a newly inserted record, even though the scope_identity() will return null or
2: do I now have to add a column to each table, keep the uniqueidentifier (as all my tables are unified by that relationship) and also add a pk field as an int identity(1,1) primary_key, in order to be able to pull the scope_identity() on insert...

View 3 Replies View Related

SQL 2012 :: Add Unique Numeric Identifier For Text Field With Same Name?

Aug 21, 2014

I have a list of movies that show throughout the year. I would like to assign a unique numeric identifier to each text field.

I have provided some sample data with the output I would like. The Movie_ID in the sample data is just made up, feel free to assign any numeric identifier, preferably of the same length but not a necessity.

create table dbo.Movie_Pre_Fix
(
Report_Monthint,
IDint,
Movie_NameVarchar(50)
);
insert into dbo.Movie_Pre_Fix (Report_Month, ID,Movie_Name)
VALUES
(201406,0721312144,'SAW'),

[code]....

View 6 Replies View Related

DB Design :: Foreign Key Referencing Non Unique Field In Server

Oct 12, 2012

I ran into an interesting situation.  I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem.  I found FK's that are referencing columns that do not have a unique constraint or a unique index. 

I don't know the history of the database but was there a time in SQL Server history where this would have been possible?  I scripted out the tables and created it in a test database.  When I run the script to create the FK I get the following message. 

I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2.  Currently the database is running on SQL Server 2008 Ent.

ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1])
REFERENCES [dbo].[table2] ([col2])

There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.

View 4 Replies View Related

Unique Key&#39;s And Multiple NULLS?

Jul 10, 2001

Hi,

I have 3 columns that I'd like to set as a key, however I do have multiple instances of NULLs for all 3 columns. Thus when I try to create the key, it stops because there are multiple instances of NULLs. I looked at using procedure that check each entry made into the table, but I couldn't figure out how to deny an INSERT/UPDATE request. Anyone have any better methods of implimenting the unique key?

Ilya

View 3 Replies View Related

Unique Which Allows Multiple Nulls

Jul 16, 2006

Hi,
I have a non-cluster index on the filed, I need to make it non-cluster index and unique which allows multiple nulls, how should I do this with an sql statement?

View 6 Replies View Related

Retrieving Multiple Values From One Field In SQL Server For Use In Multiple Columsn In Reports

Mar 30, 2007

I am trying to create a report using Reporting Services.

My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.

I can currently get one value but how to get the information I need to be able to use in my reports.

So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.

Any help would be appreciated.



Thanks.



I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.

What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report

As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.

As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem

Thanks for the suggestions that were made, I apoligize for not making the problem clearer.

Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.



--Pulls the Service Opportunity

SELECT cs.value AS "Service Opportunity"

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE ca.name = 'Service Opportunity'



--Pulls the Number of Hours

SELECT cs.value AS 'Number of Hours'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Num of Hours'



--Pulls the Person Grade Level

SELECT cs.value AS 'Grade'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ca.name ='Grade'



--Pulls the Person Number, First and Last Name and Grade Level

SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"

FROM student s

INNER JOIN cperson cs ON cs.personid = s.personid

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE cs.value =(SELECT cs.value AS 'Grade'

WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')

View 11 Replies View Related

Using Multiple Fields As The Unique Identifier

Jun 10, 2008

Please see below post

View 2 Replies View Related

Multiple Null In Unique Column, Is It Possible?

Nov 20, 2007

Hi All,



I am having problems with unique contraint, say I have a table called customer
create table customer (
id int identity(1,1) not null,
name nvarchar(50) not null,
id_card_no nvarchar(20) unique,
);
so when I do mutilple null inserts
insert into customer(name, id_card_no)
values("Customer 1", NULL);
insert into customer(name, id_card_no)
values("Customer 2", NULL);
I get an error duplicated keys on the unique column.

In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.

I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...



Are there any ways to archieve that in sql sever compact?

View 2 Replies View Related

Unique Constraint On Multiple Columns

Oct 31, 2005

Can you create a unique constraint on multiple columns, or does it haveto be implemented as a unique index?If possible can someone please post some sample code?Thanks,

View 4 Replies View Related

Multiple Null In Unique Column, Is It Possible?

Nov 20, 2007



Hi All,

I am having problems with unique contraint, say I have a table called customer
create table customer (
id int identity(1,1) not null,
name nvarchar(50) not null,
id_card_no nvarchar(20) unique,
);
so when I do mutilple null inserts
insert into customer(name, id_card_no)
values("Customer 1", NULL);
insert into customer(name, id_card_no)
values("Customer 2", NULL);

I get an error duplicated keys on the unique column.
In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.

I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...

Are there any ways to archieve that in sql sever compact?

View 5 Replies View Related

SQL Server 2008 :: Returning Unique Rows - Read Date Field?

Jul 26, 2015

I have the following query:

Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn

[Code] ....

The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?

So instead of showing this:

2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370

I'm trying to get it to look like this:

2Person112015-07-26 17:07:24.9370

Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.

2Person102015-07-26 17:07:24.9370

View 1 Replies View Related

SQL Server 2008 :: Statement Error Out Due To Source Field Being Unique Identifier

Sep 22, 2015

I have a field I am trying to bring into a SQL statement

,ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'') AS 'OriginatingLeadId'

I get this error message

Conversion failed when converting from a character string to uniqueidentifier.

the field specs' originatingleadid is attached ....

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved