WHY DO FORIEGN KEYS HAVE TO BE UNIQUE OR HAVE A CONSTRAINT?

Oct 9, 2007

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.

View 2 Replies


ADVERTISEMENT

Constraint Expression For Unique Keys

Jun 28, 2006

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 Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

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 Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

What'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?

View 1 Replies View Related

Unique Constraint Error When There Is No Constraint

May 13, 2008

We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error:
A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle
But the only PK on this table is RegTitleID.

The table structure is:
[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,

The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number.
Has anyone else experienced this?

View 3 Replies View Related

Unique Index Vs Unique Constraint

Mar 7, 2001

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

View 4 Replies View Related

Unique Constraint Vs Unique Index

Jan 20, 2006

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

Unique Keys

Jul 20, 2005

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 Related

Adding Unique Keys

Aug 22, 2006

Would 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 Related

Define Unique Keys

Oct 17, 2007

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.

View 2 Replies View Related

Elimenating Duplicate Keys With Unique Row.

Jan 25, 2000

I 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

View 3 Replies View Related

Maintaining Unique Keys When Offline

Aug 7, 2007

If 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.

View 3 Replies View Related

Add UNIQUE Constraint

May 13, 2006

Hi,I want to add unique contraint to EXISTING column in EXISTING table.I have ms sql 2005.How to do that?

View 7 Replies View Related

Unique Constraint

Nov 19, 2001

Does anyone have any Idea on how I could enforce a unique constrait across multiple tables?

View 1 Replies View Related

Unique Constraint

May 19, 2004

What is the simplest way to add a unique constraint on a field of type varchar(7) that can allow any number of <NULL>'s?

I only want to ensure that when this field is updated, it is updated with a value that has not been used.


IF EXISTS (SELECT Project FROM tbProjects WHERE Project = @cProject)
RAISERROR('Project number already used!',16,1)
ELSE
UPDATE tbProjects SET Project = @cProject WHERE ProjectID = @iProjectID
GO


Also, I cannot allow the user to chante the project field value once it is set.


Any suggestions?

Mike B

View 14 Replies View Related

Unique Constraint

Apr 4, 2008

Anybody knows how to make two columns in a table unique?

View 1 Replies View Related

UNIQUE Constraint

Aug 16, 2006

i'm just creating a table, no data as yet.

i have a varchar(15) fld with Nulls NOT allowed

i want to create a constraint that this field named ipkey be UNIQUE.

I'm having trouble with the syntax for the constraint expression.

i've typed in the SQL Server Managment Studio Express Expression field:
UNIQUE(ipkey)
and
@unq_ipkey UNIQUE(ipkey)

SQL Server Managment Studio Express complains:
"Error validating constrating, do you want to edit expression"

So - what am i doing wrong?
And thanks for taking the time to help.

View 2 Replies View Related

Unique Constraint

Dec 3, 2007

Hi,

I have a table with two column, c1 and c2. c1 is set as primary key. I want c2 to be set with unique constraint.

I choose this talbe in object explorer, right click and select modify. Then I choose "index/key" from "table designer" menu.

The problem is that in the "index/key" dialog, the "Columns" item (under General) is always c1. if I click the "..." button to popup "index column", I could only choose either "c1" or <None> under "column name" dropdownlist.

How could I choose c2 and set unique constraint on it?

Thx
Tao

View 3 Replies View Related

Constraint Unique Row

Apr 11, 2008



Hi,

I need to define a constraint , to prevent some fields of a table to be duplicated, How can i define this constraint ?

View 3 Replies View Related

A Unique Unique Constraint

May 1, 2008

Here is the table I created:

create table Test (
[recId] [int] identity(1, 1) not null,
[code] [varchar](50) not null,
[prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)),
constraint [PK_Test] primary key clustered
(
[recId]
) with fillfactor = 90 on [primary]
) on [primary]
go

insert into Test (code, prime) values ('AVA', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('CAR', cast(1 as bit))
insert into Test (code, prime) values ('CAR', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(1 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))

select *
from Test

I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:

-- This should fail
insert into Test (code, prime) values ('RLW', cast(1 as bit))


Thanks for you help!

Regards,
Anand

View 11 Replies View Related

Unique Colm Indexes And Primary Keys

Jan 25, 2006

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

View 4 Replies View Related

Sql Exception Unique Key Constraint

Oct 25, 2007

 
Hi All,
I am trying to catch a specfic unique key constraint in a table.
i my table i have two fields USERID And EMAILID and i set both to unique.
now on registration form i am checking that USERID or EMAIID is already present or not.
by taking ex.number =2627 i am not able to find which unique key constraint is getting violated.
is there any other way to find it.
thanks in advance.

View 1 Replies View Related

Can&#39;t Create Unique Constraint

Nov 3, 2000

I am attempting to create a unique constraint on an nvarchar field named theology (it is not the primary key field)
that allows nulls. The table contains multiple rows with the value of null for
field theology. The documentation says one can create a unique constraint on a
field with all unique value except for null. Here is the error message:

'testtable1' table
- Unable to create index 'IX_testtable1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX
terminated because a duplicate key was found. Most significant primary key
is ''.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See
previous errors.

Any ideas? I am creating a unique constraint and not a unique index. Is there
some other database option to set to allow this?

.

View 2 Replies View Related

Unique Constraint Question

Sep 12, 2003

when we use Unique constraint over a column it allows null values.
but then null values are duplicating, how is that?

thanks

View 3 Replies View Related

Unique Constraint With Conditioning?

May 4, 2005

Hi All:

Is there a way to create a unique constraint with 4 fields combined, such as colA, colB, colC, and colD.

but colD is for all the values but value = 3.

If this does not work out, trigger probably is the last way I would like to approach. and it is slower than constraint with many inserts.

thanks
David

View 2 Replies View Related

Add Unique Constraint On 3 Fields?

Jul 21, 2015

I want to add a unique constraint on 3 fields, to only allow the value in the field ONE time. The value will NEVER be the same for anything else. This is the table structure

Code:
Create Table Employees
(
P_Id int NOT NULL,
InstructorName varchar(255) NOT NULL,
CourseName varchar(100) NOT NULL,
DataTableName varchar(100) NOT NULL
)

I want to create a unique constraint across the fields Instructorname, CourseName, DataTableName as their is ONLY 1 instructor per course per table so those 3 fields will ALWAYS hold unique values. I think the constraint syntax would go like so, but want to check before I go butchering some sql syntax.

constraint syntax:

Code:
Alter Table Employees
Contstraint uniqueEmployee Unique (InstructorName ,CourseName ,DataTableName )

View 1 Replies View Related

Unique Constraint/index

Feb 12, 2004

I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes

Thanks in advance

View 7 Replies View Related

Unique Constraint/Index

May 7, 2008

What is the difference between unqiue constraint and unique index? What are the pros and cons? Are they interchangable?

------------------------
I think, therefore I am - Rene Descartes

View 4 Replies View Related

Add Unique Constraint Problem.

May 16, 2008

hi
i create a sample table by this code and insert some values to it :

create table test(
c1 int,
c2 int)

insert test select 1,2
insert test select 2,44
insert test select 3,56

now, i want to add new column with unique constraint by this code :

alter table test
add c3 int unique

but the following error has shown me :

Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.test' and index name 'UQ__test__2D27B809'. The duplicate key value is (<NULL>).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.

where does my problem and how to solve it ?
thaniks

View 1 Replies View Related

How Can We Have UNIQUE Constraint To The Table...

May 19, 2008

Hello...
I was confused with UNIQUE key. Its easy to create PRIMARY KEY to the Table. Now how to alter the Table to have UNIQUE key...

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 3 Replies View Related

Unique Column Constraint

Jun 6, 2008

I like to use the management console to design tables, but have
been unable to figure out how to add a unique column constraint.

I have a primary key defined in col1 and want to make col2, char(10),
unique, but keep col1 as the primary key.

I have tried the Check constraint menu path, but do not know how
to write the expression.

If I go along the Indexes/Key path, I am forced to change the primary
key which I do not want to do.

Can anyone help?

TIA, Joe

View 5 Replies View Related

Unique / Check Constraint?

Jun 19, 2008

I have a table called tblImages with the following columns:

ImageID [int]
UserID [int]
MainImg [bit]

what i need to ensure is that only one MainImage can be = 1 (true) for each userId at any one time.

any idea what i need to do?

alex

View 1 Replies View Related







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