Indexes Vs Constraints

Sep 18, 2001

When I first set up my database, I set it up with many unique constraints, but not much in the way if indexes. After reading more about the benefits of indexes, I'm thinking of deleting many of the unique constraints, and replacing them with unique indexes where appropriate.

It seems that indexes would be better than unique constraints in many cases, because you have the double benefit of enforcing uniqueness, as well as improving sorting capability. Does this make sense?

View 1 Replies


ADVERTISEMENT

CONSTRAINTS AND INDEXES

Nov 1, 2001

What is the difference between creating a Unique Constraint and Unique Index? How does creating Index/Constraint affect my performance and db size?

Thank you.

View 1 Replies View Related

Foreign Key Constraints And Indexes

Oct 12, 2006

Hi All,

I know that when you specify a PRIMARY KEY or UNIQUE constraint for a table, SQL Server creates a unique index for the primary key columns.

What about foreign keys? Does SQL Server creates an index when you create a foreign key?
I have looked in Books Online and on the Internet, but couldn't find any information.

Thanks in advance.

View 2 Replies View Related

Foreign Key Constraints And Indexes

Oct 12, 2006

Hi All,

I know that when you specify a PRIMARY KEY or UNIQUE constraint for a table, SQL Server creates a unique index for the primary key columns.

What about foreign keys? Does SQL Server creates an index when you create a foreign key?
I have looked in Books Online and on the Internet, but couldn't find any information.

Thanks in advance.

View 5 Replies View Related

Copy A Database Table With All Its Data, Indexes And Constraints To A New Table In The Same Database

Feb 4, 2008

Hi,
How can I copy a database table with all its data, indexes and constraints to a new table in the same database in sql server 2005

View 7 Replies View Related

UGH! Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 9, 2007

I know this is probably a flick of a switch but I cannot figure out which switch.  Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL.  The stored procedure queries from only one table and two columns are ignored because they are being phased out.  I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error.  I checked every column that does not allow nulls and they all have values.  I checked unique columns (ID is the only unique and is Identity=Yes in the table definition).  I checked foreign-key columns for values that are not in the foreign table and there are none.  Any ideas why do I get this? 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

View 3 Replies View Related

Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 17, 2008

Hi,
    I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that  there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.

View 7 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.

Sep 18, 2007

So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

View 4 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.

In order to do that, I have taken the records based on the fragmentation %.

But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 Replies View Related

Indexes Vs Clustered Indexes

Sep 17, 2006

What is the difference please?

View 1 Replies View Related

How Do You Do Constraints

Oct 15, 2006

I'm using MS SQL 2000 with enterprise manager I have a customers table called high_customer, it has a primary key called IDI have an invoices table called high_invoices, it has no primary keys but it does have a column for customer ID.I want to set it so if a customer is deleted from the customers table, any invoices with that customerID are deleted from the invoices table.Do I need to specify a constraint? If so how do I do it in enterprise manager.Thanks

View 1 Replies View Related

Constraints

Apr 27, 2005

Hi,
I have the following problem:
I have a table called Jobs with the fields:
JobNumber, Name, Customer, ...
And a table called Customers with the fields:
ID, Name, Address, ...
Obviously Jobs is linked to Customers with the Customer<->ID fields.  I want to set it up so that if a Customer is deleted, then any Jobs that had that customer listed now have the Customer field set to NULL.  Can I do this with a constraint, or will I need to use a trigger?
Cheers,Little'un

View 10 Replies View Related

Constraints

Feb 27, 2001

Hello,
I am not a DBA and I need to do the following.
I have 2 tables A and C and both have a common fileds say emp.Emp is pkey in C .
There can be a record in C but not in A.If a record is inserted in A then it checks for the same employee info in C .If not found it do not allow to insert a record in A.
How to go for it?
Thanks!

View 1 Replies View Related

Constraints

Feb 5, 2004

Hiya peops!!

Listen i was wondering if anyone could tell me whether they know the correct syntax for creating constraints or where i could find it on the net???


Thanks

View 3 Replies View Related

Constraints

Feb 5, 2004

Hiya peops
im creating a constraint using enterprise manager for 1 of my tables n was wondering how u constructively create a constraint expression, there is a specific style,right??

Thanks

View 1 Replies View Related

Constraints

Nov 20, 2007

hi there,

i have added my tables and defined foreig keys and primary keys for my table, but when i try to put the data it gives me some error reagards to parent key..and it wont let me drop the table... can anybody tell me how to drop the foregin key..i have tried (

ALTER TABLE module DROP CONSTRAINT ATTENDANCEID_FK;)
this formula and it wont work,

heres my tables and relationships:


CREATE TABLE ATTENDANCE
(ATTENDANCEID char(4) NOT NULL,
StudID char(4) NOT NULL,
ModuleID char(4) NOT NULL,
Week1 VARCHAR(10),
Week2 VARCHAR(10),
Week3 VARCHAR(10),
Week4 VARCHAR(10),
Week5 VARCHAR(10),
Week6 VARCHAR(10),
PRIMARY KEY (ATTENDANCEID));

ALTER TABLE ATTENDANCE
ADD FOREIGN KEY (studID) REFERENCES student(studID);
ALTER TABLE ATTENDANCE
FOREIGN KEY (moduleID) REFERENCES module(moduleID));



insert into ATTENDANCE values
('F1','A1','E1','Yes','Yes','Yes','Yes','Yes','Yes');
insert into ATTENDANCE values
('F2','A2','E2','Yes','No','Yes','Yes','No','Yes');
insert into ATTENDANCE values
('F3','A3','E2','Yes','Yes','Yes','No','Yes','Yes');
insert into ATTENDANCE values
('F4','A4','E2','Yes','Yes','Yes','Yes','No','Yes');
insert into ATTENDANCE values
('F5','A5','E1''Yes','Yes','No','Yes','Yes','Yes');
insert into ATTENDANCE values
('F6','A6','E2','Yes','Yes','Yes','Yes','Yes','No');
insert into ATTENDANCE values
('F7','A7','E1','Yes','Yes','Yes','Yes','No','No');
insert into ATTENDANCE values
('F8','A8','E2','Yes','Yes','Yes','No','Yes','Yes');
insert into ATTENDANCE values
('F9','A9','E3','Yes','Yes','No','Yes','Yes','Yes');





CREATE TABLE MODULE
(ModuleID char(4) NOT NULL,
ATTENDANCEID char(4) NOT NULL,
ModuleName varchar(45),
ModuleCode varchar(6),
PRIMARY KEY (ModuleID));

ALTER TABLE MODULE
ADD FOREIGN KEY (attendanceID) REFERENCES attendance(attendanceID);


insert into MODULE values
('E1','F1','Workshop Research and Project Implementation','Im3023');
insert into MODULE values
('E2','F2','Advanced Database Development','SD3042');
insert into MODULE values
('E3','F3','Advacned Information Systems Develpment','SD3043');


charles

View 2 Replies View Related

SQL Constraints

Dec 11, 2007

I am fairly new at SQL and also new to this forum. So I don't know if this topic belongs here or not.

But anyways, I found this tutorial online. However, I am having trouble completing it. Can someone please help me?

Given the following table definitions:

CREATE TABLE USERS
(
USERID CHAR(5) PRIMARY KEY,
NAME VARCHAR(30),
RATING CHAR(1) CHECK (RATING IN (A, B,C,D)
)

CREATE TABLE ITEMS
(
ITEM_NO NUMBER PRIMARY KEY,
DESCRIPTION VARCHAR(50),
OFFERED_BY CHAR(5) REFERENCES USERS(USERID) ON UPDATE CASCADE ON DELETE CASCADE,
START_DATE DATETIME,
END_DATE DATETIME,
RESERVE_PRICE NUMBER
)

CREATE TABLE BIDS
(
USERID CHAR(5) REFERENCES USERS ON UPDATE CASCADE ON DELETE SET NULL,
ITEMNO NUMBER REFERENCES ITEMS(ITEM_NO) ON UPDATE CASCADE ON DELETE CASCADE,
BID NUMBER,
BID_DATE DATETIME,
PRIMARY KEY(USERID,ITEMNO,BID_DATE)
)

Directions: Indicate whether the statement is accepted or rejected; and if accepted indicate its effects. Evaluate each statement INDEPENDENTLY.

1) INSERT INTO ITEMS VALUES(1009, Red Bicycle,U01,1999-01-08, 1999-02-10, 30)
2) UPDATE ITEMS SET ITEMNO=1008 where ITEMNO=1001
3) DELETE FROM ITEMS WHERE ITEMNO=1005
4) INSERT INTO ITEMS VALUES(1010, Green Bicycle,U07,1999-01-08, 1999-02-10, 30)
5) UPDATE BIDS SET ITEMNO=1011 WHERE ITEMNO = 1001
6) DELETE FROM USERS WHERE USERID=U01
7) UPDATE ITEMS SET ITEMNO=1020 WHERE ITEMNO=1001
8) DELETE FROM BIDS WHERE BID > 1001


Thanks. I would greatly appreciate your help.

View 4 Replies View Related

Constraints ?

Dec 7, 2007



My table structure is something like this :

ORDER_ITEM (OrderSK, ItemNumber, Qty, ServiceSK, UnitPrice)
SERVICE (ServiceSK, Description, UnitPrice)


How could I modify the database structure to enforce the data constraint that ORDER_ITEM.UnitPrice must be equal to SERVICE.UnitPrice for the corresponding service.

View 6 Replies View Related

Constraints In SQL Server

Jan 23, 2004

i have a question about constraints in SQL server.

i have three tables:

Client
---------
clientID
name
etc...


Bank
-------
BankID (a virtual number, identity)
ClientID (FK: Client)
BankAccountNumber
BankName

Order
--------
OrderID
ClientID
BankID


a Client an have one or more bankAccounts.. if he place an order, he can select wich bankaccount will be used to pay.

how do i have to set the constraints so that the bankAccount in Order, is allways of THAT client in order.???

Thanx...

View 3 Replies View Related

Override Constraints

Jul 17, 2004

I have to change someones username in my database in a couple of tables. When I try to make any changes, I get an error that there is a constraint. Is there a way to make the changes without removing all the constraints then replacing them?
Thanks

View 1 Replies View Related

Scripting Constraints

Nov 30, 2001

Hi,

Just getting into scripting sql. I have a little script that adds and drops a default value for a column. But I cannot get it to run when the column already has a default value specified. If I didn't script the contraint and specify a name I have no name to specify when trying to drop it.

ALTER TABLE users ADD CONSTRAINT dev1 DEFAULT 1 FOR ns_email_flag
ALTER TABLE users DROP CONSTRAINT dev1

How do I remove any existing default values using script?

TIA,

Seoras.

View 2 Replies View Related

Constraints And Triggers

May 4, 2001

How does one know what a particular trigger or constraint is supposed to be doing on a table. I am just two days old on this position, and no form of documentation, at the same time I am expected to trouble-shoot some error messages. Thanks for your help.

View 3 Replies View Related

Drop FK Constraints

Apr 30, 2000

Hi folks,
Wonder if I'm onto something....everything I've read says that to drop foreign key constraints, you have to drop/recreate the table.

I've been able to dynamically create the ALTER TABLE statements for all FK constraints in a database. The way I did was a little convoluted (Access 97 linked tables and pass-through queries, cut/paste from the debug window to SQL7 QA), but it does work. More importantly, it saves the hassle of dropping/recreating tables....Someone with more expertise than I in using nested cursors could probably convert it to pure T-SQL pretty easily.

Just curious...opinions? Be glad to post the code here if that's appropriate.

Dan

View 1 Replies View Related

Check Constraints

Dec 1, 2000

How do i create a check constraints on column a so it dose not accept $ character? syntax pls.

Thanks

View 2 Replies View Related

Table Constraints

Sep 25, 2002

Can anyone recommend a good article/book on the subject? I am trying to find out more about compound primary keys. How many is “too many”? Does the number of primary key columns affect your performance? If so, how does it affect it? Should some of the constraints be handled from stored procedures?

Any response is greatly appreciated.

:cool:

View 2 Replies View Related

DTS Primary Key Constraints

Dec 29, 2006

Executed as user: companyadmin user. Violation of PRIMARY KEY constraint 'PK_tblRMAProduct'. Cannot insert duplicate key in object 'tblRMAProduct'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

View 1 Replies View Related

Check Constraints?

May 4, 2002

Hi,
I am developing a database for my company in SQL server 2000 and I have some
few problems.
Firstly.

I have a customers table and orders table in my DB:

Customers Orders
--------- ------
CustID (primary key) ----------------< CustID
. ^ ProductID
. | Quantity
. | .
. | .
etc. | etc.
|
relationship
(one to many)

What I want to do is:
1) to be able to delete a Customer and automaticaly SQL server delete all the
orders that this customer done from the Orders table.
2) If for some reason the CustomerID changes, SQL should be able to
automaticaly update the necessary fields with the new values in the Orders
table.
Finally, 3) I want to be able to insert a new customer that has an order
and update both the Customers table and Orders table automaticaly. e.g

CustID Name Address ProductID Quantity etc.
------ ---- ------- --------- -------- ----
10-003 John London 33-25 2 ...

Such a kind of insert should add automaticaly the following entries in the
two tables:

Customers Orders
--------- ------
CustID (10-003) CustID (10-003)
Name (John) ProductID (33-25)
Address(London) Quantity (2)
. .
. .
etc. etc.

A friend of mine told me that this can be done using Foreign Check constraints
in SQL server. But I do not know what to do.

Can anybody help me please?

Thank you very much.

Efthymios Kalyviotis
ekalyviotis@comerclub.gr

View 2 Replies View Related

Check Constraints

May 20, 2002

I have a question regarding the use of check constraints. I see how to set it up to make sure a value entered is one of a list using this syntax:

([eft_vc] = 'No' or [eft_vc] = 'Yes')

However, rather than have to hard code the allowable values, I'd like to have them read off another table. The logic would be as follows:

([eft_vc] in (select * from eft_t))

When I try to put this in, I get a message saying that constraints do not support subqueries. Does anyone know of a way around this?

thank you,
Darell

View 1 Replies View Related

Check Constraints??

Jul 4, 2004

Can someone help me with this check constraint. I'm trying to get it runned on MS SQL Server but it seems the syntax isn't correct

check((Derived_Val=0) or ((select count(*) from Stage_One where Task=U_Id and Quantity is null)=0))

Thanx

j2dizzo

View 4 Replies View Related

Check Constraints

Apr 7, 2005

I am Trying to add a check constraint that if the paymenttotal is 0 the column is allowed to have null and if its greater then 0 it is not allow to have null. Here is what I have so far but i get some syntax errors, See if you can see what im doing wrong and how to get this to be valid. Thanks
heres what I got so far


Code:

ALTER TABLE Invoices WITH CHECK
PaymentDate SMALLDATETIME NULL,
CHECK (PaymentTotal = 0)
PaymentDate SMALLDATETIME NOT NULL,
CHECK (PaymentTotal > 0)



I also had this before i changed it to that and I got syntax erros as well but i dunno which is closer.


Code:

ALTER TABLE Invoices [WITH CHECK]
ADD CHECK (PaymentTotal = 0), PaymentDate SMALLDATETIME NULL,
ADD CHECK (PaymentTotal > 0), PaymentDate SMALLDATETIME NOT NULL

View 1 Replies View Related

Check Constraints

Mar 2, 2006

Can someone give me some good examples of check constraints that I can apply to my fields.

For example can I apply a constraint on a name field, at the moment I use one for the date but would like to know many more.

So if anyone has any useful check constraints handy then please tell.

Thanks
Liz

View 4 Replies View Related

How To Bypass FK Constraints???

Aug 24, 2005

Hi,

I have relationships built for several tables and I want to bypass the FK restraints when deleting/truncating a table. The FK works fine when some one is trying to delete soemthing within the application, but on an administrative level, it is becoming VERY BOTHERSOME! Any help will be greatly welcomed!

__
Updated 8/24 @ 16:24

I tried a little experiment. I took the constraint off of my two main tables, "manufacturers" and "products". I tried to TRUNCATE the tables again and I got the same error message. My products seems to have other contraints with two other tables. I checked out the two tables and both of them are empty. What's the deal!?! Again, any help is welcomed!

__
Updated 8/24 @ 17:02
Ok so I tried to disable the constraints: ALTER products NOCHECK CONSTRAINT ALL, but it didn't work with truncating.

View 1 Replies View Related







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