Constraints in SQL..?

[kenjoswe]

Hi all,

In Access I can define relations between the tables so that I cannot delete a customer with sales orders.

How can this be established in SQL-server7?

/Kent J.

 

[moonshadow]

Lots of ways, but if you're used to using Access, try creating a new diagram. Expand the database in Enterprise Manager, right-click. Select New Diagram. Add the tables your interested in then drag-and-drop the fields between the tables, just as in Access.

 

[kenjoswe]

Well, I've tried that.
It's not possible when the tables are already created.

/Kent J.

 

[moonshadow]

It is possible - assuming the data in the tables conforms to the relationships you're trying to establish. If not, you'll have to go through a data cleansing operation to sort out your data first e.g. deleting orphan records.
Alternatively, you can use SQL, thus:

ALTER TABLE doc_exe ADD
/* Add a column referencing another column in table doc2 */
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc2(column_a)
WITH NOCHECK

The WITH NOCHECK option tells SQL Server, not to check the current data. Don't forget, if you need to establish referential integrity between two tables, the referenced table must have a primary key on the columns you are trying to reference.
If you still have problems, try scripting the two tables in question, and post the creation script here, I might be able to spot the problem.