Continue INSERT after key violation?

Hi there.
I have a simple table with a UNIQUE constraint on one field. I wish to
populate this table using a stored procedure that returns the equivalent
results set like so:
INSERT INTO MyTable EXECUTE p_GetMyResultsSet
My stored proc returns unique, distinct results each time it's called
(unique within each call!). The problem I'm having though is that, if the
stored proc returns any record that already exists in the table, the whole
statement quits with a 'constraint violation'.
I would like the statement to continue inserting the results - only leaving
out records which fail the criteria - is this possible?
e.g: 1. p_GetMyResultsSet returns one row with a key value of '1' - record
is inserted into the table OK.
2. p_GetMyResultsSet returns 4 rows with key values of 1, 2, 3, 4.
What happens now: none of the records are inserted because '1' already exist
s.
What I'd like to hapen: records 2, 3, 4 to be inserted.Can't you change the proc so that it only returns the rows that don't
exist? For example:
SELECT x, ...
FROM foo
WHERE NOT EXISTS
(SELECT *
FROM MyTable
WHERE x = foo.x) ;
If not, you could insert the results of the proc to a temp table and
then to MyTable using the same WHERE NOT EXISTS logic.
David Portas
SQL Server MVP
--|||Yes - I think I'll have to use the temp table approach. I'm restricted from
using the 'not exists' because the stored proc needs to be available for use
by other apps and processes- the "MyTable" population is just one process o
f
many using the proc to generate data...
Thanks for the help!
"David Portas" wrote:

> Can't you change the proc so that it only returns the rows that don't
> exist? For example:
> SELECT x, ...
> FROM foo
> WHERE NOT EXISTS
> (SELECT *
> FROM MyTable
> WHERE x = foo.x) ;
> If not, you could insert the results of the proc to a temp table and
> then to MyTable using the same WHERE NOT EXISTS logic.
> --
> David Portas
> SQL Server MVP
> --
>

Constraints...

I am creating the following constraints on a table and keep getting some
errors... what is the problem'
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Eligibilitytb] ADD
CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
Error Message
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
'RecipDemotb', column 'OriginalRecipid'.
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database 'EDITPS',
table 'RecipDemotb', column 'OriginalRecipid'.Sounds like the tables already have data that violate the foreign key
constraint. Try this:
SELECT * FROM dbo.CMStb
WHERE OriginalRecipid NOT IN
(
SELECT OriginalRecipid FROM dbo.RecipDemotb GROUP BY OriginalRecipid
)
If you get rows back, this is why the constraint fails.
(Is the "tb" at the end of the object name meant to stand for "table"? If
so, and Celko spots it, heaven help you.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:9C5527C1-A010-4BC6-B78C-0755FF93BCD5@.microsoft.com...
> I am creating the following constraints on a table and keep getting some
> errors... what is the problem'
> ALTER TABLE [dbo].[CMStb] ADD
> CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[Eligibilitytb] ADD
> CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> Error Message
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
> 'RecipDemotb', column 'OriginalRecipid'.
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database
'EDITPS',
> table 'RecipDemotb', column 'OriginalRecipid'.
>

Constraints on Table Field

Hi,

I have a table which saves attachment files.

I need to create a constraint on table which will restrict the size of attachments in the table. How this can be done? I know we can create a check constraint on table but how to restrict the field size? Pls. advise. Thanks in advance.

Are you storing your files as text in the table? If so you might be able to do something checking the datalength like:

create table dbo.testor
( targetFile nvarchar(max)
check ( datalength(targetFile) <= 30000 )
)

|||

Thanks for your response.

I am using image as datatype to attach files. I need to restrict this column size to only accept 50 kb size.

Thanks,

Constraints in SQL server

I wanna know how to define constraints in sql server.
everytime i try something it says "error validating constraint'

does someone have an example how a constraint have to look like?

PS: i want to the constraint that check's this:
i have an order.. an order haves orderlines
and we have a 'contract' .. and contract have 'contractlines'

.. an order can became a 'contract' .. and some of the orderlines will be 'contractlines'

so a contract have a FK:orderID.. and a contractline have FK:contractID&orderlineID

i want to check if the orderlineID in contractline exists in the correspondending order..

>>>something like:
orderlineID exists in ( select orderlineID from orderline left join
order on order.orderID = orderLine.orderID left join contract on
contract.orderID = order.orderID left join contract on contractLine.contractID = contract.contractIDIt sounds like you want foreign key constraints:

create table Orders
(
OrderID int not null,
primary key (OrderID),
)
create table OrderLines
(
OrderID int not null,
OrderLineID int not null,
primary key (OrderID, OrderLineID),
)
create table Contracts
(
ContractID int not null,
OrderID int null,
primary key (ContractID),
foreign key (OrderID) references Orders(OrderID),
)
create table ContractLines
(
ContractID int not null,
ContractLineID int not null,
OrderID int null,
OrderLineID int null,
primary key (ContractID, ContractLineID),
foreign key (OrderID, OrderLineID) references OrderLines(OrderID, OrderLineID),
)

I don't think this is a very good design for several reasons, including the following:
1) I used nulls
2) I have no idea if the various IDs are natural or surrogate keys
3) I assumed a lot of things about how the four concepts orders, contracts, order lines and contract lines are related

constraints for e-mail

Is it possible to write a check constraint for a column in a table which says that the entry must contain a '@.' and a '.'? (It's an email address field)
I'm guessing this is the best way to do it anyway,
Thanks in advance, newbie!USE Northwind
GO

CREATE TABLE myTable99(
Col1 varchar(255)
CONSTRAINT myTable99_ck1
CHECK ( LEN(Col1)=LEN(REPLACE(Col1,'@.',''))+1
AND LEN(Col1)>LEN(REPLACE(Col1,'.',''))
)
)

INSERT INTO myTable99(Col1) SELECT 'brett.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brettkaiser@.somewhere'
INSERT INTO myTable99(Col1) SELECT 'brett.kaiser somewhere.com'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||What? You don't like it?

:-o|||How about

check (col1 like '%@.%.%')|||Originally posted by MCrowley
How about

check (col1 like '%@.%.%')

That'll allow more than 1 @. sign...

CREATE TABLE myTable99(
Col1 varchar(255)
CONSTRAINT myTable99_ck1
CHECK ( (col1 like '%@.%.%')
)
)

INSERT INTO myTable99(Col1) SELECT 'brett.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brett@.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brettkaiser@.somewhere'
INSERT INTO myTable99(Col1) SELECT 'brett.kaiser somewhere.com'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||thanks guys thats great! :)

sorry i took so long to get back|||While i'm on..

could anyone help me with this trigger!!

Create Trigger Maintain_employeedata
On payroll_tbl
After update on employee_tbl
For Each Row
Begin
Insert into payroll_tbl
(payroll_id, employee_id)
Values
(:New.payroll_id, :Old.employee_id);
End;

This does not seem to work, it produces the following errors:

Server: Msg 156, Level 15, State 1, Procedure Maintain_SurveyorData, Line 3
Incorrect syntax near the keyword 'on'.
Server: Msg 170, Level 15, State 1, Procedure Maintain_SurveyorData, Line 9
Line 9: Incorrect syntax near ':'.

Basically when I add a new employee to my employee table it should add this employee to the payroll table via their employee_id.. and subsequently create a new payroll_id to match. the payroll_id is the primary key of the table and should be an increment of the last one (eg if we have got up to P0007, it should create P0008 for the new person).

Is this actually possible? its really bugging me!|||First it looks like you have an Oracle background...

Second, you're inserting in to the same table, after an update to the sane table...I don;t understand this..

Third SQL does not have a FOR EACH ROW syntax, you have to join to the virtual table "inserted" (oracles new) and "deleted (oracles old)..

fourth, the error message is for something else..

CREATE TRIGGER <triiger_name> ON Table

is correct

AFTER UPDATE (which isn't required, is the default) doesn't use the ON Table syntax...|||ah, well actually I don't have a background in any form! that was just syntax i picked up, and i'm only working on MS SQL server using the query analyzer :|

I have these ammendments, but I don't think ive quite grasped what youre saying?

Create Trigger Maintain_employeedata on payroll_tbl
After update on employee_tbl
For Each Row
Begin
Insert into payroll_tbl
(payroll_id, employee_id)
Values
(:inserted.employee_id, :deleted.payroll_id);
End;|||I don't pretend to understand what you're trying to do...

but at least this should compile...

You need to make sure you identify the key of the row...is it employeeId?

CREATE TRIGGER Maintain_employeedata ON Employee_tbl
FOR UPDATE
BEGIN
INSERT INTO payroll_tbl (payroll_id, employee_id)
SELECT i.employee_id, d.payroll_id
FROM inserted i
INNER JOIN deleted d
ON i.key of the row = d.key of the row
END
GO|||yeah ive altered the keys so that they are right. i.employee_id and d.payroll_id

but i have

Server: Msg 156, Level 15, State 1, Procedure Maintain_employeedata, Line 3
Incorrect syntax near the keyword 'BEGIN'.|||I forgot the AS...place in a line before the BEGIN|||It's actually easier to help if you post the DDL of the table, and some sample data, and some sample DML (The Updates statements)

Sample data should look like

INSERT INTO myTable99(col1,col2,col3,ect)
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
ect

DDL looks like

CREATE TABEL mtTable99 (Col1, char(1), col2 int, ect...

You'll get answers that are correct, and fatse that way...

MOO

Constraints and Foreign Keys....

Hi,
I am currently in the design phase. I have a question about the possibility
of creating a certain type of constraint. So far, I don't think its
possible.
For the sake of the example say I have two entities, Trees and Cars. I keep
track of entities in another entity called Entity Types. This would contain
car and tree as a record. Then I have another list of entities called
characteristics. Certain charateristics will only apply to the car, others
to the tree and some to both. Example characteristics would be model,
manufacturer, species, and name.
So I created an Entity Assignable Charateristics. This will hold which
charateristics can be applied to which entity. Some of those will be
required, others won't. I could add an attribute "Required" to keep track o
f
this.
Charateristics for instances cars or trees will be stored in either Entity
Charateristics or in Car Charateristics and Tree Charateristics.
Here's my question. If in Entity Assignable Charateristics there are
indicated certain required charateristics for trees, is there a way I can
enforce that in Entity Charateristics or Tree Charateristics.
Thanks in advance.
Pete>> I have two entities, Trees and Cars. I keep track of entities in another
entity called Entity Types. <<
This is an OO approach to the world and NOT an RDBMS. Separate types
of entieis have their own tables; there is no such monster as a
"magical, vague, general thingie" set. To be is to be something in
particular, not everythign in general.|||Start by googling for "EAV design flaws".
Inability to impose appropriate integrity constraints is the primary
drawback of approaches similar to the one you propose.
Anith|||Hi,
Thanks for the response. I just want to make sure I understand you
correctly. You think that I should have Trees and Cars. Then you think I
should have Tree Assignable Charaterisics and Car Assignable Charateristics.
These hold all the possible charateristics for a tree or car respectively.
This makes sense.
Now if I create a Tree Charateristics entity, which holds all the
charateristics for each instance of a tree, how can I make sure that the
charateristics that are placed into this entity come from the Tree Assignabl
e
Charateristics subset or Charateristics?
Additionally, how would required charateristics play into this?
Thanks again.
Pete
"--CELKO--" wrote:

> This is an OO approach to the world and NOT an RDBMS. Separate types
> of entieis have their own tables; there is no such monster as a
> "magical, vague, general thingie" set. To be is to be something in
> particular, not everythign in general.
>|||Hi,
Thanks for the comment. The reason I have the entities I do is so people
can easily create a new tree or car or charateristic. However, one person
may wish to make the charaterisic model for a car required, another may not.
Since you seem to think I have a flaw in my design, could you suggest anothe
r
way I might accomplish what it is I am trying to do? Thanks again.
Peter
"Anith Sen" wrote:

> Start by googling for "EAV design flaws".
> Inability to impose appropriate integrity constraints is the primary
> drawback of approaches similar to the one you propose.
> --
> Anith
>
>|||>> The reason I have the entities I do is so people can easily create a new
Without a comprehensive knowledge of your business model, others cannot
possibly determine the characteristics of an entity of your enterprise.
Rather than thinking about trees, cars and characteristics, think of trees
and cars as a set of entities having their own attributes. In loose terms,
each form an entity type & therefore belong to separate tables.
Think of each table as a set of propositions, or statement of facts.
In a nutshell, with a relational database, rows represent facts about real
world entities, column values represent their properties and relational
operations answer the questions about them.
Anith|||Thanks for the comments. Let me start by saying I agree with your statement
.
However, it is a business requirement that a customer can create custom
attributes. That is in essence what I need to figure out.
I certainly do not want to give the user the ability to add a column to a
table, so it seems as though I need to use this EAV design. I am troubled b
y
it since I seem to be losing the ability to put certain restraints on the
data. Do you know of another way to handle custom attributes?
Thanks again.
"Anith Sen" wrote:

> Without a comprehensive knowledge of your business model, others cannot
> possibly determine the characteristics of an entity of your enterprise.
> Rather than thinking about trees, cars and characteristics, think of trees
> and cars as a set of entities having their own attributes. In loose terms,
> each form an entity type & therefore belong to separate tables.
>
> Think of each table as a set of propositions, or statement of facts.
> In a nutshell, with a relational database, rows represent facts about real
> world entities, column values represent their properties and relational
> operations answer the questions about them.
> --
> Anith
>
>|||Pete,
On what basis does a customer create those attributes? How does one make
sure the attributes are appropriate to the entity that is being represented
in the table? How does one constrain the values to be drawn from a domain
specific to that attribute?
It sure sounds like a serious misunderstanding of business requirements.
There is no such thing as a custom attribute. Either an entity has an
attribute or it does not. It is up to the database designer to determine all
the applicable attributes of relevance pertaining to an entity in the
conceptual model, before starting logical design. In general, when one
chooses to ignore this exercise, often he is forced to compensate by forcing
the user to share the burden of creating tables, columns, constraints etc.
Generally, the database design flows from conceptual -> logical -> physical
levels.
-- Understand the business model thoroughly so that you will know the
entities, relationships among them and the attributes involved.
-- From this business model ( also known as conceptual schema ) applying
relational principles, map them relational tables. Use proven design
guidelines like normalization, predicate uniqueness principle etc. to
eliminate data redundancy. A good relational design allows the data to be
represented without application or task bias.
-- Based on the capabilities of the SQL DBMS, tune up the physical model so
the data can be efficiently manipulated and retrieved.
Many a time, designers introduce complexity at the logical level in an
attempt to compensate for an incomplete business model. Similarly complexity
is often introduced at the physical level when trying to compensate for a
mediocre logical model as well.
Anith|||Yes, it is the same. Initially, they called it New Design Priciple and later
renamed to the Principle of Orthogonal design.
Anith|||Thanks for the response. These are all questions I am grappling with. The
example that I started with illustrates this point well. I need to create
new trees, new cars and new charateristics that could be applied to either.
We are an ISV so customers are supposed to be able to create their own
characteristics (i.e. attributes). If you know of another way to handle thi
s
(without changing the table structure), please let me know. Any thoughts
would be great.
Thanks again.
"Anith Sen" wrote:

> Pete,
>
> On what basis does a customer create those attributes? How does one make
> sure the attributes are appropriate to the entity that is being represente
d
> in the table? How does one constrain the values to be drawn from a domain
> specific to that attribute?
> It sure sounds like a serious misunderstanding of business requirements.
>
> There is no such thing as a custom attribute. Either an entity has an
> attribute or it does not. It is up to the database designer to determine a
ll
> the applicable attributes of relevance pertaining to an entity in the
> conceptual model, before starting logical design. In general, when one
> chooses to ignore this exercise, often he is forced to compensate by forci
ng
> the user to share the burden of creating tables, columns, constraints etc.
> Generally, the database design flows from conceptual -> logical -> physica
l
> levels.
> -- Understand the business model thoroughly so that you will know the
> entities, relationships among them and the attributes involved.
> -- From this business model ( also known as conceptual schema ) applying
> relational principles, map them relational tables. Use proven design
> guidelines like normalization, predicate uniqueness principle etc. to
> eliminate data redundancy. A good relational design allows the data to be
> represented without application or task bias.
> -- Based on the capabilities of the SQL DBMS, tune up the physical model s
o
> the data can be efficiently manipulated and retrieved.
> Many a time, designers introduce complexity at the logical level in an
> attempt to compensate for an incomplete business model. Similarly complexi
ty
> is often introduced at the physical level when trying to compensate for a
> mediocre logical model as well.
> --
> Anith
>
>

constraints

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??
Thanksscript it out of EM and it'll give you the syntax

Constraints

Hi,
What all things are required to be known abt a constraint (FKey) inorder to
re-create it perfectly after dropping it?
I make that sure from the following information schemas : INFORMATION_SCHEMA
.REFERENTIAL_CONSTRAINTS,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and INFORMATION_SCHEMA.COLUMNS
Could anyone guide me if i'm on the right track?Hi,
Have a look into books online for the system procedure SP_FKEYS
Thanks
Hari
MCDBA
"Sheetal" <anonymous@.discussions.microsoft.com> wrote in message
news:89BEB1F5-34A9-40BF-8323-E99D7054D4FF@.microsoft.com...
> Hi,
> What all things are required to be known abt a constraint (FKey) inorder
to re-create it perfectly after dropping it?
> I make that sure from the following information schemas :
INFORMATION_SCHEMA. REFERENTIAL_CONSTRAINTS,
> INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
INFORMATION_SCHEMA.COLUMNS
> Could anyone guide me if i'm on the right track?

Constraints

Can any one tell me regarding the foreign key constraints like I wanna
findout which columns doesn't contain the foreign key constraint so that I
can create it...
Thanks in advance.
Hi Joh,
Why do you want create Foreign key constrain for all the columns in a table
, Probably i understood your question wrongly.
What you could do is all the constrains specificatins can be view in the
below information schema view. Have a look into it and analyze ur
requirement.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Can any one tell me regarding the foreign key constraints like I wanna
> findout which columns doesn't contain the foreign key constraint so that I
> can create it...
>
> Thanks in advance.
>
|||No you took my question wrongly... I want to make sure Primary Key &
Foreign Key constraints are there in my database... nothing would be missed
for instance...
Emp Dept
EmpID DeptID
DeptID DeptName
EmpName
According to the above situation I have to make sure in the emp table there
should be a foreign key constraint on DeptID column.. Is there any query
through which I can make sure about it... hope now you understand my
question.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi Joh,
> Why do you want create Foreign key constrain for all the columns in a
table[vbcol=seagreen]
> , Probably i understood your question wrongly.
> What you could do is all the constrains specificatins can be view in the
> below information schema view. Have a look into it and analyze ur
> requirement.
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
I
>
|||Hi,
I dont have a query for this with me. I personally recommend you to use MS
Visio / Erwin to do the logical / physical data model with all relationship.
But for validating you could use INFORMATION_SCHEMA.TABLE_CONSTRAINTS system
view.
Thanks
Hari
SQL Server MVP
can you please work with the system view
INFORMATION_SCHEMA.TABLE_CONSTRAINTS? This will help
"Joh" <joh@.mailcity.com> wrote in message
news:OAQTHygYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> No you took my question wrongly... I want to make sure Primary Key &
> Foreign Key constraints are there in my database... nothing would be
> missed
> for instance...
> Emp Dept
> EmpID DeptID
> DeptID DeptName
> EmpName
> According to the above situation I have to make sure in the emp table
> there
> should be a foreign key constraint on DeptID column.. Is there any query
> through which I can make sure about it... hope now you understand my
> question.
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> table
> I
>

constraint?

Hi,
I was wondering how i could set up a constraint if i want to prevent
duplicates.
For example, in a table where i have 4 columns:
-id (identity and primary)
-contactid
-type
-period
One can have values like:
id | contactid | type | period
1 c123 1 11
2 c355 | 2 | 11
Now what i would like to do is if someone inserts a record like id 1 and
the period is 12, then the constraint should apply. If someone inserts a
record like id 2 and the period is 12 then it's ok.
Is this possible?Somethinmg like this for example:
ALTER TABLE your_table
ADD CONSTRAINT ak_constraint_name
UNIQUE (contactid, period) ;
David Portas
SQL Server MVP
--|||"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>
I'm still not quite sure what you are after here. You can create a UNIQUE
constraint/index across multiple columns in a table.
For your first example, a UNIQUE constraint across (id, contactid, type)
would do the trick.
What is the difference with record id 2? Is it because the type column has
pipe symbols in it? I'm not following.
If it is more complex than the simple UNIQUE constraint, then you can either
apply your rules in a stored procedure, or with a trigger.
Rick Sawtell
MCT, MCSD, MCDBA|||Create a unique index, duplicate inserts will fail.
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>|||Jason
What is about type column? What is the purpose of this column?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>|||Rick Sawtell wrote:
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
>
> I'm still not quite sure what you are after here. You can create a UNIQU
E
> constraint/index across multiple columns in a table.
> For your first example, a UNIQUE constraint across (id, contactid, type)
> would do the trick.
> What is the difference with record id 2? Is it because the type column h
as
> pipe symbols in it? I'm not following.
> If it is more complex than the simple UNIQUE constraint, then you can eith
er
> apply your rules in a stored procedure, or with a trigger.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Hi Rick,
The pipes are nothing, it's a seperator.
The difference is, that a contact with type 1 may be inserted only once.
Type 2 can have several inserts.
So i only want to constrain contacts which has type 1.|||create table semi_unique(id int primary key, stype int, uq int, u1 as
case when stype=1 then null else id end)
-- uq must be unique for type 1 only
create unique index u1 on semi_unique(uq, u1)
insert into semi_unique(id, stype, uq) values(1,1,1)
-- should fail
insert into semi_unique(id, stype, uq) values(2,1,1)
-- should succeed
insert into semi_unique(id, stype, uq) values(3,2,1)
insert into semi_unique(id, stype, uq) values(4,2,1)
select * from semi_unique
drop table semi_unique|||Alexander Kuznetsov wrote:
> create table semi_unique(id int primary key, stype int, uq int, u1 as
> case when stype=1 then null else id end)
> -- uq must be unique for type 1 only
> create unique index u1 on semi_unique(uq, u1)
> insert into semi_unique(id, stype, uq) values(1,1,1)
> -- should fail
> insert into semi_unique(id, stype, uq) values(2,1,1)
> -- should succeed
> insert into semi_unique(id, stype, uq) values(3,2,1)
> insert into semi_unique(id, stype, uq) values(4,2,1)
> select * from semi_unique
> drop table semi_unique
>
Hi it worked, but what if i add another column to it.

Constraint: Table xGUID and Deleted

Hi,
I have a database table which contains a column with a GUID. In addition I
have column that contains a Deleted flag. I now would like to create a
contraint that makes sure that there is only one undeleted GUID.
What is the expression for this?
Thanks for any advice
Pete
Pete SmithProbably best to maintain this via a insert/update trigger.
create trigger x on tbl for insert, update
as
if exists (select guid from tbl t where guid in (select guid from inserted i
where i.flag <> 'D') and t.flag <> 'D' group by GUID having count(*) > 1)
begin
rollback tran
raiserror ('multiple undeleted', 16, -1)
end
go
"Pete Smith" wrote:

> Hi,
> I have a database table which contains a column with a GUID. In addition I
> have column that contains a Deleted flag. I now would like to create a
> contraint that makes sure that there is only one undeleted GUID.
> What is the expression for this?
> Thanks for any advice
> Pete
> --
> Pete Smith
>
>|||You can do this using an indexed view
create view UndeletedGUIDs
with schemabinding
as
select GUID
from dbo.mytable
where flag<>'d'
create unique clustered index IX_UndeletedGUIDs on UndeletedGUIDs(GUID)

constraint/trigger in sql server in Asp.net

In my Project

i want to check the date at the time of insert in A-Table

that it should be Greater than (>) Date Defined in B-Table

Note:-B-table have only one record

so plz tell me how can i do using Sql-Server Backend only

Hopefully you are using a stored proc wherein you could:

CREATE PRO...@.param1 ..@.param2datetimeDeclare @.datefromBdatetimeSELECT @.datefromB = datecolumnFROM tableBIF @.param2 > @.datefromBBEGIN--Do the insert hereENDELSEBEGIN

-- dp whatever needs to be done return 0END

|||But where i defined that stored procedure in my code|||Looks like you need DB 101. Check out documentation for "stored procedures".

Constraint/identity which allows duplicate null fields

hi,

I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?

Thank you.

Make your value a foreign key constraint you can take the IDENTITY property of another table make sure your table is UNION compatible, create an index on another column in the same table and add that column in it through the new feature called INDEX Column include turn on the IGNORE_DUP_KEY option and you have a duplicate proof column. Now let me explain it UNION is a SET operator that performs implicit distinct by eliminating duplicates, and by adding it to an index it can also use the physical IGNORE_DUP_KEY which also eliminates duplicates and your column can get the benefits of an index without actually being the index. Run a search for UNION,IGNORE_DUP_KEY and INDEX COLUMN include in the BOL(books online). I am assuming you know you have to spend time with Management Studio to create this, post again if you still have question. Hope this helps.|||

Can't you just check before you do an INSERT?

IF NOT EXISTS( SELECT * FROM YourTable WHERE LinkedItemID = @.somevalue)

BEGIN

-- do the insert

END

|||

ndinakar:

Can't you just check before you do an INSERT?

IF NOT EXISTS( SELECT * FROM YourTable WHERE LinkedItemID = @.somevalue)

BEGIN

-- do the insert

END

That will do what IGNORE_DUP_KEY will do but a UNION will also eliminate duplicates in a Query of more than one table, the main difference between UNION and UNION ALL. It is the reason UNION comes with more restriction than UNION ALL.

|||

I am confused why we need a UNION, foreign key constraint, INDEX COLUMN etc in this scenario. IGNORE_DUP_KEY will work if the index has already been created with that option.

|||

(My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question )

This was the original poster's need I just showed all can be achived with a combination of SET algebra and the physical. UNION will eliminate duplicates in a query with another column that will introduce duplicates in the result and it comes with compatibility requirement in the table definition, you are checking for duplicates only during insert. Index column include will let that column to be included in a Unique index which require NOT NULL by default, so use the IGNORE_DUP_KEY option and SQL Server just toss the duplicates and continue any insert because ADO.NET does more than one insert. One more thing foriegn key constraints are nullable by ANSI SQL definition.

http://msdn2.microsoft.com/en-us/library/ms190806.aspx

|||

hi,

thanks all of you for your assistance. I still think your solution is far too complicated - there must be some easier way? Why I don't do a check before INSERT - it's because there are about 10 stored procs in my database which work with this particular field and it would be so much easier to do this by some kind of constraint (or even trigger?!)...

|||

EDIT

The quick solution is Unique Constraint because it allows NULLs and the other easy option is through the index column include in a Unique index, you just include it and use the IGNORE_DUP_KEY option, it is not complicated. UNION is complicated to set up if it was not included during the database design. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191166.aspx

constraint view

Hi,

I want to know which table my foreign is reference to

for instance I have this statement

ALTER TABLE BANK ADD CONSTRAINT BANKING_R_154 FOREIGN KEY (
COM_CODE ,
ACCOUNT_CODE ) references BANK_GLT

I want execute ine select * from information_schema.I_don't_know

to get the table BANK_GLT from constraint BANKING_R_154

How can I do this?

cheers,

Alessandro

Select * from INFORMATION_SCHEMA.Referential_Constraints RC

INNER JOIN INFORMATION_SCHEMA.Constraint_Column_usage CCU

ON RC.Constraint_Catalog = CCU.Constraint_Catalog AND

RC.Constraint_Schema = CCU.Constraint_Schema AND

RC.Constraint_Name = CCU.Constraint_Name

Where Table_Name = 'SomeTable' --for a table, use Constraint_Name for querying on Constraint Names
--

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks,

but, I would like to know which table my tables is reference to and which columns as well

cheers,

Constraint using SQL query?

I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
....random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
Amit
Amit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Constraint using SQL query?

I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
.);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Constraint using SQL query?

I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Constraint situation.

The following problem has a long history.
In the past I have asked quetions about this.
Parent table
PK_id
removed_indication
the other fields
Child table
CK_id
removed_indication
PK_id
parent_removed indication
the other fields
Constraints we want to implement.
1. A child should have a parent.
This is just a FK PK relationship
2. If a parent has children (not removed) then the parent can not be
removed.
(Remark 1: A child can be removed at any time.
Remark 2: Removal is done by changing the removed indication.)
Is it possible to implement this as a constriant.
We prefer not to use triggers or business logic.
At the end of this message the create script for the two tables and
constraint 1.
thanks for your time,
ben brugman
CREATE TABLE [dbo].[Parent1] (
[PK_id] [int] NOT NULL ,
[removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[child1] (
[CK_id] [int] NULL ,
[removed_indication] [int] NULL ,
[PK_id] [int] NULL ,
[parent_removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
(
[PK_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[child1] ADD
CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
(
[PK_id]
) REFERENCES [dbo].[Parent1] (
[PK_id]
)
GO
"ben brugman" <ben@.niethier.nl> wrote in message
news:eSEEy$DjEHA.1184@.TK2MSFTNGP12.phx.gbl...
> The following problem has a long history.
> In the past I have asked quetions about this.
> Parent table
> PK_id
> removed_indication
> the other fields
> Child table
> CK_id
> removed_indication
> PK_id
> parent_removed indication
> the other fields
> Constraints we want to implement.
> 1. A child should have a parent.
> This is just a FK PK relationship
> 2. If a parent has children (not removed) then the parent can not be
> removed.
> (Remark 1: A child can be removed at any time.
> Remark 2: Removal is done by changing the removed indication.)
> Is it possible to implement this as a constriant.
> We prefer not to use triggers or business logic.
> At the end of this message the create script for the two tables and
> constraint 1.
>
Yes it is possible. The trick is that if any column in the foreign key side
of the constraint is null, then the constraint is not enforced. SO make the
relationship on PK_ID and removed_indication. Then to detach a child, set
removed_indication to null. You may want to rename the column since it's
valid values are 0 and NULL, not 1 and 0, but you get the idea.
Anyway I have to mention that the most obvious way to detach a child row is
by simply nulling it's PK_ID, but I suppose you have your reasons.
Here's an altered script.
David
drop table child1
drop table parent1
go
CREATE TABLE [dbo].[Parent1] (
[PK_id] [int] NOT NULL ,
[removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[child1] (
[CK_id] [int] NULL ,
[removed_indication] [int] NULL ,
[PK_id] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
(
[PK_id]
) ON [PRIMARY]
alter table parent1
add constraint ck_parent1_removed_incication
check (removed_indication = 0 or removed_indication is null)
GO
ALTER TABLE [dbo].[Parent1] ADD
CONSTRAINT [UK_Parent1]
UNIQUE(pk_id,removed_indication)
go
ALTER TABLE [dbo].[child1] ADD
CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
(
PK_id,removed_indication
) REFERENCES [dbo].[Parent1] (
PK_id,removed_indication
)
GO
alter table child1
add constraint ck__child1_removed_incication
check (removed_indication = 0 or removed_indication is null)
go
insert into parent1 (pk_id,removed_indication) values(1,0)
insert into child1 (ck_id,pk_id,removed_indication) values(1,1,0)
go
--will fail with fk violation
delete from parent1 where pk_id = 1
--detaches the child row by nulling part of the fk constraint
update child1 set removed_indication = null
where pk_id = 1
--now this will work, even though the child still exists
delete from parent1 where pk_id = 1
|||Thanks for your attention and your solution.
Is your solution a reasonable solution, because
the column is used to join on as well. (parent join child).
And (in the future) it is used to search on as well ?
(Find all removed children).
My question actually is : Is this a trick, is this a good practise
or commonly used ?
ben

> Yes it is possible. The trick is that if any column in the foreign key
side
> of the constraint is null, then the constraint is not enforced. SO make
the
> relationship on PK_ID and removed_indication. Then to detach a child, set
> removed_indication to null. You may want to rename the column since it's
> valid values are 0 and NULL, not 1 and 0, but you get the idea.
> Anyway I have to mention that the most obvious way to detach a child row
is
> by simply nulling it's PK_ID, but I suppose you have your reasons.
(Yes the same reason why we are not simply deleting the child).

> Here's an altered script.
> David
> drop table child1
> drop table parent1
> go
> CREATE TABLE [dbo].[Parent1] (
> [PK_id] [int] NOT NULL ,
> [removed_indication] [int] NULL ,
> [information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[child1] (
> [CK_id] [int] NULL ,
> [removed_indication] [int] NULL ,
> [PK_id] [int] NULL ,
> [information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
> CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
> (
> [PK_id]
> ) ON [PRIMARY]
> alter table parent1
> add constraint ck_parent1_removed_incication
> check (removed_indication = 0 or removed_indication is null)
> GO
> ALTER TABLE [dbo].[Parent1] ADD
> CONSTRAINT [UK_Parent1]
> UNIQUE(pk_id,removed_indication)
> go
> ALTER TABLE [dbo].[child1] ADD
> CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
> (
> PK_id,removed_indication
> ) REFERENCES [dbo].[Parent1] (
> PK_id,removed_indication
> )
> GO
> alter table child1
> add constraint ck__child1_removed_incication
> check (removed_indication = 0 or removed_indication is null)
> go
> insert into parent1 (pk_id,removed_indication) values(1,0)
> insert into child1 (ck_id,pk_id,removed_indication) values(1,1,0)
> go
> --will fail with fk violation
> delete from parent1 where pk_id = 1
> --detaches the child row by nulling part of the fk constraint
> update child1 set removed_indication = null
> where pk_id = 1
>
> --now this will work, even though the child still exists
> delete from parent1 where pk_id = 1
>
>
|||"ben brugman" <ben@.niethier.nl> wrote in message
news:ehUgXUEjEHA.2812@.tk2msftngp13.phx.gbl...
> Thanks for your attention and your solution.
> Is your solution a reasonable solution, because
> the column is used to join on as well. (parent join child).
> And (in the future) it is used to search on as well ?
> (Find all removed children).
As always you should support the FK with an index on child1.
create index ix_fk_child1 on child1(pk_id,removed_indicator)
this index will support searching and simplify locking.

> My question actually is : Is this a trick, is this a good practise
> or commonly used ?
It should work fine, but as I mentioned it would be more common to simply
make child1.pk_id nullable and null it out to detach a child1 row from a
parent1 row. You may have some special need to retain the relationship
information in memo form, and therefore to introduce another column simply
to disable the relationship.
David

Constraint situation.

The following problem has a long history.
In the past I have asked quetions about this.
Parent table
PK_id
removed_indication
the other fields
Child table
CK_id
removed_indication
PK_id
parent_removed indication
the other fields
Constraints we want to implement.
1. A child should have a parent.
This is just a FK PK relationship
2. If a parent has children (not removed) then the parent can not be
removed.
(Remark 1: A child can be removed at any time.
Remark 2: Removal is done by changing the removed indication.)
Is it possible to implement this as a constriant.
We prefer not to use triggers or business logic.
At the end of this message the create script for the two tables and
constraint 1.
thanks for your time,
ben brugman
CREATE TABLE [dbo].[Parent1] (
[PK_id] [int] NOT NULL ,
[removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[child1] (
[CK_id] [int] NULL ,
[removed_indication] [int] NULL ,
[PK_id] [int] NULL ,
[parent_removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
(
[PK_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[child1] ADD
CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
(
[PK_id]
) REFERENCES [dbo].[Parent1] (
[PK_id]
)
GO"ben brugman" <ben@.niethier.nl> wrote in message
news:eSEEy$DjEHA.1184@.TK2MSFTNGP12.phx.gbl...
> The following problem has a long history.
> In the past I have asked quetions about this.
> Parent table
> PK_id
> removed_indication
> the other fields
> Child table
> CK_id
> removed_indication
> PK_id
> parent_removed indication
> the other fields
> Constraints we want to implement.
> 1. A child should have a parent.
> This is just a FK PK relationship
> 2. If a parent has children (not removed) then the parent can not be
> removed.
> (Remark 1: A child can be removed at any time.
> Remark 2: Removal is done by changing the removed indication.)
> Is it possible to implement this as a constriant.
> We prefer not to use triggers or business logic.
> At the end of this message the create script for the two tables and
> constraint 1.
>
Yes it is possible. The trick is that if any column in the foreign key side
of the constraint is null, then the constraint is not enforced. SO make the
relationship on PK_ID and removed_indication. Then to detach a child, set
removed_indication to null. You may want to rename the column since it's
valid values are 0 and NULL, not 1 and 0, but you get the idea.
Anyway I have to mention that the most obvious way to detach a child row is
by simply nulling it's PK_ID, but I suppose you have your reasons.
Here's an altered script.
David
drop table child1
drop table parent1
go
CREATE TABLE [dbo].[Parent1] (
[PK_id] [int] NOT NULL ,
[removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[child1] (
[CK_id] [int] NULL ,
[removed_indication] [int] NULL ,
[PK_id] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
(
[PK_id]
) ON [PRIMARY]
alter table parent1
add constraint ck_parent1_removed_incication
check (removed_indication = 0 or removed_indication is null)
GO
ALTER TABLE [dbo].[Parent1] ADD
CONSTRAINT [UK_Parent1]
UNIQUE(pk_id,removed_indication)
go
ALTER TABLE [dbo].[child1] ADD
CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
(
PK_id,removed_indication
) REFERENCES [dbo].[Parent1] (
PK_id,removed_indication
)
GO
alter table child1
add constraint ck__child1_removed_incication
check (removed_indication = 0 or removed_indication is null)
go
insert into parent1 (pk_id,removed_indication) values(1,0)
insert into child1 (ck_id,pk_id,removed_indication) values(1,1,0)
go
--will fail with fk violation
delete from parent1 where pk_id = 1
--detaches the child row by nulling part of the fk constraint
update child1 set removed_indication = null
where pk_id = 1
--now this will work, even though the child still exists
delete from parent1 where pk_id = 1|||Thanks for your attention and your solution.
Is your solution a reasonable solution, because
the column is used to join on as well. (parent join child).
And (in the future) it is used to search on as well ?
(Find all removed children).
My question actually is : Is this a trick, is this a good practise
or commonly used ?
ben
> Yes it is possible. The trick is that if any column in the foreign key
side
> of the constraint is null, then the constraint is not enforced. SO make
the
> relationship on PK_ID and removed_indication. Then to detach a child, set
> removed_indication to null. You may want to rename the column since it's
> valid values are 0 and NULL, not 1 and 0, but you get the idea.
> Anyway I have to mention that the most obvious way to detach a child row
is
> by simply nulling it's PK_ID, but I suppose you have your reasons.
(Yes the same reason why we are not simply deleting the child).
> Here's an altered script.
> David
> drop table child1
> drop table parent1
> go
> CREATE TABLE [dbo].[Parent1] (
> [PK_id] [int] NOT NULL ,
> [removed_indication] [int] NULL ,
> [information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[child1] (
> [CK_id] [int] NULL ,
> [removed_indication] [int] NULL ,
> [PK_id] [int] NULL ,
> [information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
> CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
> (
> [PK_id]
> ) ON [PRIMARY]
> alter table parent1
> add constraint ck_parent1_removed_incication
> check (removed_indication = 0 or removed_indication is null)
> GO
> ALTER TABLE [dbo].[Parent1] ADD
> CONSTRAINT [UK_Parent1]
> UNIQUE(pk_id,removed_indication)
> go
> ALTER TABLE [dbo].[child1] ADD
> CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
> (
> PK_id,removed_indication
> ) REFERENCES [dbo].[Parent1] (
> PK_id,removed_indication
> )
> GO
> alter table child1
> add constraint ck__child1_removed_incication
> check (removed_indication = 0 or removed_indication is null)
> go
> insert into parent1 (pk_id,removed_indication) values(1,0)
> insert into child1 (ck_id,pk_id,removed_indication) values(1,1,0)
> go
> --will fail with fk violation
> delete from parent1 where pk_id = 1
> --detaches the child row by nulling part of the fk constraint
> update child1 set removed_indication = null
> where pk_id = 1
>
> --now this will work, even though the child still exists
> delete from parent1 where pk_id = 1
>
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:ehUgXUEjEHA.2812@.tk2msftngp13.phx.gbl...
> Thanks for your attention and your solution.
> Is your solution a reasonable solution, because
> the column is used to join on as well. (parent join child).
> And (in the future) it is used to search on as well ?
> (Find all removed children).
As always you should support the FK with an index on child1.
create index ix_fk_child1 on child1(pk_id,removed_indicator)
this index will support searching and simplify locking.
> My question actually is : Is this a trick, is this a good practise
> or commonly used ?
It should work fine, but as I mentioned it would be more common to simply
make child1.pk_id nullable and null it out to detach a child1 row from a
parent1 row. You may have some special need to retain the relationship
information in memo form, and therefore to introduce another column simply
to disable the relationship.
David

Constraint situation.

The following problem has a long history.
In the past I have asked quetions about this.
Parent table
PK_id
removed_indication
the other fields
Child table
CK_id
removed_indication
PK_id
parent_removed indication
the other fields
Constraints we want to implement.
1. A child should have a parent.
This is just a FK PK relationship
2. If a parent has children (not removed) then the parent can not be
removed.
(Remark 1: A child can be removed at any time.
Remark 2: Removal is done by changing the removed indication.)
Is it possible to implement this as a constriant.
We prefer not to use triggers or business logic.
At the end of this message the create script for the two tables and
constraint 1.
thanks for your time,
ben brugman
CREATE TABLE [dbo].[Parent1] (
[PK_id] [int] NOT NULL ,
[removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[child1] (
[CK_id] [int] NULL ,
[removed_indication] [int] NULL ,
[PK_id] [int] NULL ,
[parent_removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
(
[PK_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[child1] ADD
CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
(
[PK_id]
) REFERENCES [dbo].[Parent1] (
[PK_id]
)
GO"ben brugman" <ben@.niethier.nl> wrote in message
news:eSEEy$DjEHA.1184@.TK2MSFTNGP12.phx.gbl...
> The following problem has a long history.
> In the past I have asked quetions about this.
> Parent table
> PK_id
> removed_indication
> the other fields
> Child table
> CK_id
> removed_indication
> PK_id
> parent_removed indication
> the other fields
> Constraints we want to implement.
> 1. A child should have a parent.
> This is just a FK PK relationship
> 2. If a parent has children (not removed) then the parent can not be
> removed.
> (Remark 1: A child can be removed at any time.
> Remark 2: Removal is done by changing the removed indication.)
> Is it possible to implement this as a constriant.
> We prefer not to use triggers or business logic.
> At the end of this message the create script for the two tables and
> constraint 1.
>
Yes it is possible. The trick is that if any column in the foreign key side
of the constraint is null, then the constraint is not enforced. SO make the
relationship on PK_ID and removed_indication. Then to detach a child, set
removed_indication to null. You may want to rename the column since it's
valid values are 0 and NULL, not 1 and 0, but you get the idea.
Anyway I have to mention that the most obvious way to detach a child row is
by simply nulling it's PK_ID, but I suppose you have your reasons.
Here's an altered script.
David
drop table child1
drop table parent1
go
CREATE TABLE [dbo].[Parent1] (
[PK_id] [int] NOT NULL ,
[removed_indication] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[child1] (
[CK_id] [int] NULL ,
[removed_indication] [int] NULL ,
[PK_id] [int] NULL ,
[information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
(
[PK_id]
) ON [PRIMARY]
alter table parent1
add constraint ck_parent1_removed_incication
check (removed_indication = 0 or removed_indication is null)
GO
ALTER TABLE [dbo].[Parent1] ADD
CONSTRAINT [UK_Parent1]
UNIQUE(pk_id,removed_indication)
go
ALTER TABLE [dbo].[child1] ADD
CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
(
PK_id,removed_indication
) REFERENCES [dbo].[Parent1] (
PK_id,removed_indication
)
GO
alter table child1
add constraint ck__child1_removed_incication
check (removed_indication = 0 or removed_indication is null)
go
insert into parent1 (pk_id,removed_indication) values(1,0)
insert into child1 (ck_id,pk_id,removed_indication) values(1,1,0)
go
--will fail with fk violation
delete from parent1 where pk_id = 1
--detaches the child row by nulling part of the fk constraint
update child1 set removed_indication = null
where pk_id = 1
--now this will work, even though the child still exists
delete from parent1 where pk_id = 1|||Thanks for your attention and your solution.
Is your solution a reasonable solution, because
the column is used to join on as well. (parent join child).
And (in the future) it is used to search on as well ?
(Find all removed children).
My question actually is : Is this a trick, is this a good practise
or commonly used ?
ben

> Yes it is possible. The trick is that if any column in the foreign key
side
> of the constraint is null, then the constraint is not enforced. SO make
the
> relationship on PK_ID and removed_indication. Then to detach a child, set
> removed_indication to null. You may want to rename the column since it's
> valid values are 0 and NULL, not 1 and 0, but you get the idea.
> Anyway I have to mention that the most obvious way to detach a child row
is
> by simply nulling it's PK_ID, but I suppose you have your reasons.
(Yes the same reason why we are not simply deleting the child).

> Here's an altered script.
> David
> drop table child1
> drop table parent1
> go
> CREATE TABLE [dbo].[Parent1] (
> [PK_id] [int] NOT NULL ,
> [removed_indication] [int] NULL ,
> [information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[child1] (
> [CK_id] [int] NULL ,
> [removed_indication] [int] NULL ,
> [PK_id] [int] NULL ,
> [information] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Parent1] WITH NOCHECK ADD
> CONSTRAINT [PK_Parent1] PRIMARY KEY CLUSTERED
> (
> [PK_id]
> ) ON [PRIMARY]
> alter table parent1
> add constraint ck_parent1_removed_incication
> check (removed_indication = 0 or removed_indication is null)
> GO
> ALTER TABLE [dbo].[Parent1] ADD
> CONSTRAINT [UK_Parent1]
> UNIQUE(pk_id,removed_indication)
> go
> ALTER TABLE [dbo].[child1] ADD
> CONSTRAINT [FK_child1_Parent1] FOREIGN KEY
> (
> PK_id,removed_indication
> ) REFERENCES [dbo].[Parent1] (
> PK_id,removed_indication
> )
> GO
> alter table child1
> add constraint ck__child1_removed_incication
> check (removed_indication = 0 or removed_indication is null)
> go
> insert into parent1 (pk_id,removed_indication) values(1,0)
> insert into child1 (ck_id,pk_id,removed_indication) values(1,1,0)
> go
> --will fail with fk violation
> delete from parent1 where pk_id = 1
> --detaches the child row by nulling part of the fk constraint
> update child1 set removed_indication = null
> where pk_id = 1
>
> --now this will work, even though the child still exists
> delete from parent1 where pk_id = 1
>
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:ehUgXUEjEHA.2812@.tk2msftngp13.phx.gbl...
> Thanks for your attention and your solution.
> Is your solution a reasonable solution, because
> the column is used to join on as well. (parent join child).
> And (in the future) it is used to search on as well ?
> (Find all removed children).
As always you should support the FK with an index on child1.
create index ix_fk_child1 on child1(pk_id,removed_indicator)
this index will support searching and simplify locking.

> My question actually is : Is this a trick, is this a good practise
> or commonly used ?
It should work fine, but as I mentioned it would be more common to simply
make child1.pk_id nullable and null it out to detach a child1 row from a
parent1 row. You may have some special need to retain the relationship
information in memo form, and therefore to introduce another column simply
to disable the relationship.
David