0
votes
2answers
39 views

How to I establish a 1 to 1 relationship

I have a table Nurse: CREATE TABLE [dbo].[Nurse]( [EmployeeId] [int] IDENTITY(1,1) NOT NULL, [WardId] [int] NOT NULL, CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED ( [EmployeeId] ASC ...
0
votes
1answer
45 views

I see a constraint in my table thats not in INFORMATION SCHEMA?

When I expand the database "Constraints" object, I see 2 of them named DF_DocumentD_Docum_3F466844 and df_documentdistqty, but when I select to view everything from the "Information Schema.TABLE ...
9
votes
2answers
950 views

foreign key constraint violation issue

I have identified 3 situations. A student with no enrollments. A student with enrollments but no grades. A student with enrollments and grades. There is a trigger on the enrollments table to ...
1
vote
0answers
32 views

Clarify unique constraint and index, cannot grasp the concepts to 100% [duplicate]

When reading about UNIQUE, I understand the concept but when it comes to the practical part of it, I get confused and I'm unsure of what it means. What I do understand, is that there are two ways to ...
2
votes
3answers
253 views

Listing indexes and constraints

I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me. The database table I am looking at has ...
2
votes
1answer
73 views

Confused on how a value gets added with the DEFAULT constraint

A recent definition I've read about Default Constraints: DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added. I believe I've seen code ...
2
votes
1answer
38 views

How can I model and enforce constraints on a categorical item contained in a collection?

Scenario: There is a collection of items where each item belongs to 1 category. There is a collection of buckets which may contain at most 1 item from each category. Oh, and an item may belong to at ...
2
votes
1answer
123 views

Two relations to same table in SQL Server

I've got two tables. When I delete a row in Table1, I want to set the ID of that row in all the rows of Table2 to NULL. I've got two relationships on Table1. The problem is: I can only set one delete ...
0
votes
1answer
70 views

Is it possible to have a unique key condition based on another value?

I have a table with the following columns: ID, STRING_VALUE, ENUM_VALUE, CATEGORY_IDENTIFIER I'd like to create a condition that won't allow two identical ENUM_VALUEs, but only if their ID is also ...
2
votes
1answer
101 views

Does a SQL server unique constraint partially or fully fail a multi-row write?

If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?
0
votes
2answers
98 views

Accept null value on parent column in multipart relationship

EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in CHECK constraints. I would appreciate your help if you have a better answer. ...
3
votes
1answer
179 views

On which filegroup will constraints be created by default when adding them to a table on a filegroup and how should they be separeated?

We have multiple filegroups, that look something like this: PRIMARY (default) app_data app_index When I create a table on filegroup app_data CREATE TABLE Person ( [PersonID] INT NOT NULL ...
1
vote
1answer
148 views

Unique constraint on multiple nullable columns Sql Server

We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null). We are using this table to figure out which price to assign various combos ...
2
votes
1answer
95 views

How can I change Referential Integrity from a Unique Key Constraint to a Primary Key?

(without dropping foreign keys) I have migrated a SQL Server CE database to SQL Server 2012, however, referential integrity was enforced via unique key constraints on the surrogate INT identity ...
2
votes
3answers
2k views

Unique constraint across two columns

I need to add a constraint with two columns that says if any given value is present in one of the columns, then: 1) It cannot be duplicated in the same column. 2) It cannot be duplicated in the ...
2
votes
2answers
73 views

Lock hints on preconditions

Assuming for a moment that FK references are not available, what lock hints should be placed on the preconditions of the following example to prevent deadlocks and inconsistent data? (This is a ...
1
vote
1answer
109 views

How can I achieve a unique constraint with two fields? [duplicate]

I have a table with e.g. Name and IsDeleted fields. I want to add a row constraint so that only one Name value can have IsDeleted as 'false'. There can be many duplicate Name values, but they must all ...
2
votes
2answers
2k views

Constraints based on other columns

Is it possible to limit what values are allowed in a coulumn based off of other values in the row? For example, my table: ID Test_mode Active -- --------- ------ 1 1 Null 2 0 ...
1
vote
1answer
599 views

SQL Azure: Drop Constraint and Index

This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake. Here is my question: When dropping Constraints, are their indexes dropped as well? ...
1
vote
2answers
1k views

Only one NULL for one part of primary key

I have two date columns : "start date" and "finish date". I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one ...
4
votes
1answer
297 views

I need help using hierarchyid datatype and requiring a unique constraint on a varchar field

I want to resolve a bunch of tables that had parent->child->grandchild relationships into a single table using hierarchyid identifiers. Here is what I came up with based on what I had. CREATE ...
3
votes
1answer
267 views

Restrict record to be update or deleted according to dates inside row for SQL Server 2005

I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at the subscriber that still existed at the publisher. I now know that if someone updates, ...
3
votes
3answers
5k views

Altering nullability of column in SQL Server

Say I have the following table: CREATE TABLE test(test_id int not null identity primary key, field1 int not null, field2 int ); CREATE INDEX IDX_test_field1 ON test(field1); CREATE INDEX ...
1
vote
2answers
156 views

How do I create a rule for a Primary Key

I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together: p001 b201 I know I need to create a rule and bind that rule to the primary ...
2
votes
3answers
483 views

SQL Server : explicitly create an index on a primary key and unique fields

UPDATED: I'll make the question clearer as the first answer isn't quite what I was looking for. How can I execute this create table statement and ensure that the two automatically created indexes ...
5
votes
2answers
146 views

Unique Contraint/Index Based On Values

I have a table that has the following column definitions: ID (INT, PK) Name (VarChar) Active (Bit) Bunch_of (Other_columns) Question: I want to have a Constraint on Name/Active such that we can ...
3
votes
2answers
5k views

What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-) I ...
3
votes
2answers
1k views

Having a unique ID for 2 different tables

We would like to use two different tables, one will hold an object when it is 'Active', and the other will hold the object once it becomes'Non-Active'. The ID is therefore unique per both tables ...
4
votes
1answer
2k views

How do you create a relationship to a non-primary key in SQL Server?

I have a Users table which has two columns a primary key called UserID and another column called UserName. UserID (int) PK UserName (varchar(256) They are both unique but I decided for reasons to ...
3
votes
2answers
825 views

Temporarily disable checking of constraints

I'm bulk inserting rows into a database and I'm finding (through the query analyser) that the foreign key constraints are taking about 20% of the insertion time. I've tried creating a separate index ...
4
votes
1answer
3k views

How to write a check constraint to disallow empty varchar fields?

On SQL Server not nullable varchar columns can hold empty strings. Under ORACLE they can't, because varchar2 treat '' as equivalent to NULL. If you design a database schema suitable for both RDBMS ...
1
vote
2answers
987 views

SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?

We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.) Both databases are identical with many foreign-key constraints on tables. ...
4
votes
6answers
2k views

Any way around unique index 16 column max

According to the CREATE INDEX documentation: Up to 16 columns can be combined into a single composite index key. We've got a table with ~18 columns that need to form a unique combination. This ...
9
votes
2answers
5k views

Check constraint only one of three columns is non-null

I have a (SQL Server) table that contains 3 types of results: FLOAT, NVARCHAR(30), or DATETIME (3 separate columns). I want to ensure that for any given row, only one column has a result and the other ...
2
votes
1answer
618 views

Trusted Constraints and NOT FOR REPLICATION

Version and Build: SQL Server 2005 SP4 (9.0.5000) As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic. I don't know if there is really a ...
4
votes
2answers
688 views

How to mark an item as “Expired” when it's ExpiryDate value is in the past?

I have a table of items, each of which has a Status and ExpiryDate. What is the best way of enforcing this: when ExpireyDate > DateTime.Now update Status to "expired" I could have a stored ...
9
votes
3answers
3k views

Dropping a constraint (index) on a column

How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...
9
votes
2answers
2k views

Unique Constraints on Nullable Columns in SQL Server 2005

In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered ...
39
votes
5answers
11k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
6
votes
2answers
922 views

Dropping duplicate|redundant Unique Constraint from FILESTREAM table

I have a table with a FILESTREAM column, and it has two unique constraints specified for the same FILESTREAM column, ie: ALTER TABLE [dbo].[TableName] ADD CONSTRAINT ...