>I.o. StockItems think Parcels.
>
>Will this do?
>
>A Transaction can be a Purchase. In that case a new Parcel is created.
>
>For consistency reasons, I put all my FK nullable.
>
>

>CREATE TABLE dbo.Transactions
>	(
>	Transactions_Id int identity(1,1)  NOT NULL,
>	Transactiontypes_id int null,
>	Parcels_Id int null,
>	TransactionDate date not null,
>	D010Transactiontype char(3) not null default N'NA',
>	D010Transaction nchar(7) not null default N'NA',
>	D020Transactiontype nchar(3) not null default N'NA',
>	D020Transaction nchar(5) not null default N'NA',
>	Goods nchar(1) not null,
>	TransactionWeight decimal(18,2) not null,
>	Amount decimal (18,2),
>	AmountSold decimal (18,2),
>	Creationdate datetime not null ,
>	Lastupdatedate datetime default getDate()
>	)  ON [PRIMARY]
>GO
>ALTER TABLE dbo.Transactions ADD CONSTRAINT
>	PK_Transactions PRIMARY KEY CLUSTERED 
>	(
>	Transactions_Id
>	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>
>
>
>CREATE TABLE dbo.Parcels
>	(
>		Parcels_Id int NOT NULL,
>		Origins_Id int NULL,
>		CreationTransaction_Id int NULL,
>		DestinationTransaction_Id int NULL,
>		D020number nchar(8) NOT NULL default N'NA',
>		D020wrapper nchar(8) NOT NULL default N'NA',
>		Goods nchar(1) NOT NULL,
>		ParcelWeight decimal(18, 2) NOT NULL,
>		Cost decimal(18, 2) NOT NULL default 0,
>		InventoryCalculationstatus nchar(1) NOT NULL,
>		Sold decimal(18, 2) NOT NULL default 0,
>		ParcelsStatus nchar(1) NOT NULL,
>		Creationdate datetime not Null,
>		Lastupddatedate datetime default getDate()
>	)  ON [PRIMARY]
>GO
>ALTER TABLE dbo.Parcels ADD CONSTRAINT
>	PK_Parcel PRIMARY KEY CLUSTERED 
>	(
>	Parcels_id
>	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>
>alter table dbo.Transactions 
>	with check 
>	add constraint FK_Transactions_Parcels 
>	Foreign key (Parcels_id) 
>	references Dbo.Parcels (Parcels_id)
>alter table dbo.Transactions
>	with check 
>	add constraint FK_Transactions_Transactiontypes 
>	Foreign key (Transactiontypes_id) 
>	references dbo.Transactiontypes (Transactiontypes_id)
>alter table dbo.Parcels
>	with check 
>	add constraint FK_Parcels_CreationTransaction_id 
>	Foreign key (CreationTransaction_id) 
>	references dbo.Transactions (Transactions_id)
>alter table dbo.Parcels
>	with check 
>	add constraint FK_Parcels_DestinationTransaction_id 
>	Foreign key (DestinationTransaction_id) 
>	references dbo.Transactions (Transactions_id)
>alter table dbo.Origins
>	with check
>	add constraint FK_Origins_Parcels_id
>	foreign key (Parcels_id)
>	references dbo.Parcels (Parcels_id)
>

>>>Assuming a StockItems Table and a Transactions Table. If I want the Transactions Table to have a Foreign Key into the StockItems, and the StockItems to have a foreign keys into Transactions, there's no way I will be able to add a record in both tables (some Transactions will add StockItems) during the same transaction if I make both Foreign Keys non nullable.
>>>
>>>a) Is there?
>>>b) Is it ok the set foreign keys nullable?
>>>c) Is it not best practice to cross reference like this?
>>>d) is there anything else wrong in my reasoning?
>>
>>
>>Someone might jump in with a reply, but I want to ask this to confirm.
>>
>>Can you post the table structure for both tables, and confirm what your PK/FK relationships are?
>>
>>Thanks

This is a bit non-traditional and complex relation. Also, are you sure you want to have LastUpdatedDate to be getdate() and not CreatedDate. Logically I'd think the first date should be updated every time a row is updated and the first date should be populated once when row is created.