I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.
Hello,I plan to create a table with 3 unique keys.Combination of three fields has to be unique for each row in a table thatare vendor ID (char 8), vendor name (char 40), and vendor office (5).Will it be okay to have a unique key which has a long character such asvendor name?How should I index those three fields? Those fields will be searched manytimes.RCW
View 2 Replies View RelatedWould anyone please instruct how to prevent the duplicate record bysetting the unique keys on the ms sql server? i've been checking theduplicate record as front-end and i found out if there is an internetdelay or some other reasons, it has a chance to store the duplicateddata into the database. so i realized it has to be done on the back-endside.for example, if i have three columns (office code, office id, officesection) as a unique key, how can i setup this? thanks in advance.
View 1 Replies View RelatedI have a large table that consists of the columns zip, state, city, county. The primary
key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips. So in effect I only have one row per unique key.
Randy Garland
if you just want a list of all rows with duplicate zipcodes then ...
SELECT * FROM TableName WHERE zip IN ( SELECT zip FROM TableName
GROUP BY zip HAVING COUNT(*)>1 )
Duncan
Duncan, I tried this but it does not return one row per key.
Randy Garland
if i have a table which defines a rule as "combination of two fieldmust be unique", how can I write this in a constraint expressionsection?i started learning more about ms sql side to handle all the necessaryrules in back-end instead of front-end.also any good learning links, references, or book recommandations?thanks
View 5 Replies View RelatedIf you have a "Orders" table that is being sync'd to subscribers that are ocassionaly offline, and the subscribers add rows to their local Orders table. When they go online to sync with the published "Orders" table, how do you handle keeping the "OrderId" field unique?
Example:
Both salespeople sync the following data down:
OrderId Desc
1 Order 1
2 Test Order
Both salespeople go offline and add orders
Salesperson 1 adds:
OrderId Desc
3 Joes Order
Salesperson 2 adds:
OrderId Desc
3 Kathys Order
Now, when they go back online, they both will sync their orders up to the main database and they both have the OrderId of 3.
How do I go about protecting rows from deletion in this scenerio?
Rule 1 The Administrator Users Account may not be deleted
Rule 2 All Groups have Administrator as a member, and the Administrator cannot be removed.
Rule 3 All Groups have the Administrators Group as a member, and the Administrators Group cannot be removed.
Four tables:
Users Table (
UID bigint Identity seeded with 1234 Primary key
UserID varchar(30) NOT NULL UNIQUE
)
INSERT FIRST RECORD (this record needs to be protected from deletion)
UID = 1234
UserID='Admininstrator'
INSERT FIRST RECORD (this record and others can be deleted)
UID = 1235
UserID='Test User 1'
Groups Table (
GID bigint Identity seeded with 1234 Primary key
GroupName varchar(30) NOT NULL UNIQUE
)
INSERT FIRST RECORD (this record needs to be protected from deletion)
GID = 1234
UserID='Admininstrators'
INSERT SECOND RECORD (this record and others can be deleted)
GID = 1235
UserID='Test Group 1'
Group_Members Table (
GID bigint NOT NULL //points to the group's ID and can't be unique
UID bigint NOT NULL //points to the members UserID and can't be unique
)
INSERT FIRST RECORD (this record needs to be protected from deletion because UID points to the Administrator)
GID = 1234
UID = 1234
INSERT SECOND RECORD (this record and others can be deleted because UID does not point to the Administrator.)
GID = 1234
UID = 1235
Group_Group_Members Table (
GID bigint NOT NULL //points to the group's ID and can't be unique
GGID bigint NOT NULL //points to the group members GID and can't be unique
)
INSERT FIRST RECORD (this record needs to be protected from deletion because GGID points to the Administrators Group.)
GID = 1234
GGID = 1234
INSERT SECOND RECORD (this record and others can be deleted because GGID does not point to the Administrators Group.)
GID = 1234
GGID = 1235
I have tried using foriegn keys, constraints an every thing else, but I hit a brick wall because FK requires the ke to be primary (btw is UNIQUE).
Any help would be appreciated.
I have a deal table, each of these investments must be unique. I created a int pk : idDeal. Does that make sense or should i just use the deal colm being it has a unique constraint,
Reguarding indexes, should i make the auto # colm my pk and make that the clustered index? and put another index on the Deal Colmn? Any suggestions welcomed
Thank you
Hi there ...here comes a tricky one.
I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables.
This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!
I can find those rows by
SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0
WHERE EXISTS (
SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1
WHERE table0.ParentREF = table1.ParentREF
AND table0.UniqueName = table1.UniqueName
AND table0.OID != table1.OID
)
ORDER BY ParentREF, UniqueName, ModifiedUTC desc
...but I struggle to make the required SQL (SP?) to update the "invalid" rows.
Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!
ATM the preferred (cause easiest) way is to rename the invalid rows with
UniqueName = OID
because if I use any other name I risk to create another double entry.
Thanks in advance to whoever can help me
Hello,
I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.
The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold
1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.
A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.
I would like to have some tips, recommendations and alternatives for what I should do in this case.
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
View 6 Replies View RelatedThis index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies View RelatedHelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies View RelatedWhat's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies View Related
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code
Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword
and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Thank You
Dominic Mancl
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies View RelatedI have to use a CTE based on conditions. But I am not able to write a common CTE and access it on different conditions.
Now I am duplicating the CTE definition inside each if condition. But I expects a solution like bellow to reduce code duplication.
For example
---- I need a common definition of CTE like this.
with CustomGroup( id)
as
(
Select id from Groups g
Inner join GroupStatus s on s.Id = g.Id
Where s.Status = €˜Active€™
)
IF @DisplyStatus=€?UserDetail€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.* from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserSimple€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.FirstName,u.LastName,u.DOB from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserWithAddress€?
begin
end
Please suggest possibilities, and your suggestions.
Hi, here am i back ..not sure it's possible to solve it in one query ...there are four tables:1) headquarters---------------hqID (primary key)hqname2) department---------------depID (primary key)depnamehqID3) reports----------repID (p. key)depIDuserID4) users--------userID (p.key)usernameI want to get in one query those four fields:hqID, hqname, depID, depname + varchar(number of distinct users that has made a report foreach department) . This last field is a concatenation.The complexity resides in the fact there is need for a COUNT among other fields ...E.g.: table reports may look like this: repID depID userID 1 1 1 2 1 1 3 1 3 4 3 6 5 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe
View 9 Replies View RelatedHi,i have a dropdownlist connected to a sqldatasource like this: <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name" DataValueField="Name"> </asp:DropDownList>What i want is to put a particular value on the top of the dd. I can't use ORDER BY.e.g. the table which feeds the dd contains this:RDAhow to put value D on top of the dd?ThanksTartuffe
View 6 Replies View RelatedGreeting SQLers,
I'm attempting to define a Default value for a new user-defined-datatype in SQL Server 6.5, but can't as the dropdown list only allow (none) value!
Anyone can help? TIA.
How to define schemaname & how to qualify schemaname in SQL
statement.
Hello, everyone:
I need to define composite PK and FK for a ERD. Could someone offer the methods that work with,
1. T-SQL
2. ERD
Thanks a lot.
ZYT
I get the word TempleMode marked in Yellow. it's to clear all recoreds from my Pivot tables linked to SQL Database.
Sub ClearAllSheets()
On Error Resume Next
'Dim pt As PivotTable
Dim Sh As Worksheet
Dim refresh As Boolean
Dim tempMode As Boolean
Dim startTime As Date
[code]...
Is there a way to define a key that puts the text 'SELECT * FROM 'into the Query Analyzer window?I must type this about 50 times a day but cannot see a simple way ofdefining a key to write it for me..(tools/customize) seems to execute everything you put in there ratherthan leave it on the screen for me to add table names etc to.thanks for your time...
View 2 Replies View Related