relations and foreign key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

relations and foreign key

Author

Topic

romeck
Starting Member

16 Posts

Posted - 2010-11-03 : 08:22:20

Hi there, im using MSSQL 2008.
I have 2 tables in one base. base "a" have primary key on column rokid and column id (key name is : PK_rokid_id). base "b" contains records that connect info from base "a" and other base. and i want to do that: when i delete row from base "a" it delet also all rows from base "b" which are connected somehow to base "a" and this row which is deleted from base "a".( i have other base whtere is only one-column PK and then there is no problem with that couse i use cascade on delete )but with base "a" i dont know how to set relationship sinc i can only connect one column from base b to one column in base a and PK in a is 2-column based.

i cannot modify base a so only key i can use is this made up from rokid,id .

Sory for my english and probably foggy explanations :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-03 : 12:20:03

When you say Base, do you mean Table?

I'm having a little trouble understanding exactly what you want. But, have you investigated using a Foreign Key? If you use a Foreign Key you can specify that you want it to CASCADE the DELETE and it will remove the child record(s) when the parent is removed.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-03 : 12:27:25

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Rows are not records.

SQL has REFERENCES, not "connections".

Columns have scalar values and not a magical "column id". I hope you did not use an IDENTITY table property instead of a real key.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-03 : 12:52:33

I think this might be what you want. Multi-column keys are legal SQL. Here is the syntax:

CREATE TABLE Rok
(rok1_id INTEGER NOT NULL,
rok2_id INTEGER NOT NULL,
PRIMARY KEY (rok1.id, rok2_id),
..);

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
rok1_id INTEGER NOT NULL,
rok2_id INTEGER NOT NULL,
FOREIGN KEY (rok1_id, rok2_id)
REFERENCES Rok(rok1_id, rok2_id)
ON DELETE CASCADE,
..);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources