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.
| Author |
Topic |
|
ewade
Starting Member
36 Posts |
 Posted - 2003-06-20 : 18:58:37
|
| How does one implement a table-level foreign key constraint that references multiple columns from the related table? I'm trying to understand how to implement cascading keys. The SQL server documentation notes that you have to do it a certain way, then never explains or demonstrates it!The example below illustrates what I'm trying to figure out how to accomplish:CREATE TABLE A(Id CHAR(2) PRIMARY KEY);CREATE TABLE B(Id CHAR(2) NOT NULL, a_ref INT FOREIGN KEY REFERENCES A(Id), CONSTRAINT b_pk PRIMARY KEY(Id, a_ref));CREATE TABLE C(Id CHAR(2) NOT NULL,?????????, CONSTRAINT c_pk PRIMARY KEY(Id,?????));In Table C, should a column be created to hold the foreign key value that is CHAR(4), or do I need two seperate columns to hold the reference back to B's primary key columns? If that is the case, how do I implement the foreign key reference?Never stop learning! |
|
|
ewade
Starting Member
36 Posts |
Posted - 2003-06-22 : 22:07:18
|
| Allow me to simplify my question:Is this the correct way to implement what was described above?CREATE TABLE A( Id INT CHAR(2) PRIMARY KEY, Desc VARCHAR(512));CREATE TABLE B( Id CHAR(2) NOT NULL, a_id CHAR(2) NOT NULL, CONSTRAINT b_a_fk FOREIGN KEY(a_id) REFERENCES A(Id), CONSTRAINT b_pk PRIMARY KEY(Id, a_id));CREATE TABLE C( Id CHAR(2) NOT NULL, b_id CHAR(2) NOT NULL, a_id CHAR(2) NOT NULL, Desc VARCHAR(512), CONSTRAINT c_b_fk FOREIGN KEY(b_id, a_id) REFERENCES B(Id, a_id), CONSTRAINT c_pk PRIMARY KEY(Id, b_id, a_id));Never stop learning! |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2003-06-23 : 02:13:42
|
| CREATE TABLE A( Id INT PRIMARY KEY, aDesc VARCHAR(512))-- i removed char(2) from here-- Desc is a reserved word , Sql wouldnt allow you to have a column with a reserve word.Rest of code is fine.This is stright from BOL , remember if you dont give a constraint name CREATE TABLE employee ( emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'), /* Each employee ID consists of three characters that represent the employee's initials, followed by a five digit number ranging from 10000 through 99999 and then the employee's gender (M or F). A (hyphen) - is acceptable for the middle initial. */ fname varchar(20) NOT NULL, minit char(1) NULL, lname varchar(30) NOT NULL, job_id smallint NOT NULL DEFAULT 1 /* Entry job_id for new hires. */ REFERENCES jobs(job_id), job_lvl tinyint DEFAULT 10, /* Entry job_lvl for new hires. */ pub_id char(4) NOT NULL DEFAULT ('9952') REFERENCES publishers(pub_id), /* By default, the Parent Company Publisher is the company to whom each employee reports. */ hire_date datetime NOT NULL DEFAULT (getdate()) /* By default, the current system date is entered. */)-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
|
|
|
|
|
Table Level Constraints Question